· 8 years ago · Aug 04, 2018, 06:58 PM
1mysql group by, field query
2-------------------------------------------------------------------------------
3id Title unique_id type language category
4-------------------------------------------------------------------------------
51 Announcement One 111 1 1 5,6,8
62 Announcement Two 112 1 1 6
73 Announcement Three 113 1 1 7
84 Ankundigung Drei 113 1 2 7,3
95 Announcement Four 115 1 1 6
106 Announcement of another type 116 2 1 5
117 Another announcement of another type 117 2 1 7
128 Ein anderes ankundigung 117 2 2 7
139 Subtype 3 118 3 1 4
1410 Tip 3 118 3 2 4
15-------------------------------------------------------------------------------
16
17-------------------------------------------------------------------------------
18id Title unique_id type language category
19-------------------------------------------------------------------------------
201 Ankundigung Drei 113 1 2 7,3
212 Ein anderes ankundigung 117 2 2 7
223 Announcement One 111 1 1 5,6,8
234 Announcement Two 112 1 1 6
245 Announcement Four 115 1 1 6
256 Tip 3 118 3 2 4
267 Announcement of another type 116 2 1 5
27-------------------------------------------------------------------------------
28
29-- Table structure for table `anns`
30--
31
32CREATE TABLE IF NOT EXISTS `anns` (
33 `id` int(11) NOT NULL AUTO_INCREMENT,
34 `title` varchar(254) NOT NULL,
35 `unique_id` int(11) NOT NULL,
36 `type` int(11) NOT NULL,
37 `language` int(11) NOT NULL,
38 `category` varchar(254) NOT NULL,
39 PRIMARY KEY (`id`)
40) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;
41
42--
43-- Dumping data for table `anns`
44--
45
46INSERT INTO `anns` (`id`, `title`, `unique_id`, `type`, `language`, `category`) VALUES
47(1, 'Announcement One', 111, 1, 1, '2,6,8'),
48(2, 'Announcement Two', 112, 1, 1, '6'),
49(3, 'Announcement Three', 113, 1, 1, '7'),
50(4, 'Ankundigung Drei', 113, 1, 2, '7'),
51(5, 'Announcement Five', 115, 1, 1, '6'),
52(6, 'Announcement of another type', 116, 2, 1, '6'),
53(7, 'Another announcement of another type', 117, 2, 1, '7'),
54(8, 'Ein anderes ankundigung', 117, 2, 2, '7'),
55(9, 'Subtype 3', 118, 3, 1, '4'),
56(10, 'Tip 3', 118, 3, 2, '4');
57
58SELECT *
59FROM (
60 SELECT *
61 FROM `anns`
62 ORDER BY `announcement_id` , `language` DESC
63) `TA`
64GROUP BY `TA`.`announcement_id`;
65
66ORDER BY `TA`.`category` DESC"
67
68( SELECT Title,unique_id,type,language,category
69 FROM anns
70 WHERE language=2
71 UNION
72 SELECT Title,unique_id,type,language,category
73 FROM anns a
74 WHERE language=1
75 AND NOT EXISTS( SELECT 1 FROM anns a2
76 WHERE a2.unique_id=a.unique_id
77 AND a2.language=2 )
78) ORDER BY 2