我有这个表
Username Client Name Date Time Published App
1stLineS GS-HPS7 03/08/2015 9:37:44.12 PB Service Desktop
1stLineS GS-HPS7 09/07/2015 11:08:42.31 Citrix Delivery Services Console
1stLineS GARION-WIN8 29/07/2015 15:34:19.85 menar@Work2-1
user1 009733-DSK 03/08/2015 9:41:56.25 Epic
user1 009733-DSK 03/08/2015 11:53:59.78 menar@Work2-1
user1 010175-LAP 06/08/2015 9:23:15.67 menar Desktop with Acrobat
user1 NC-CITRIXDESKAA 06/08/2015 12:54:23.51 Powerplan
user1 WT008064c025aa 13/08/2015 13:44:39.34 Thin Client Desktop
user2 NCIT-008888-DSK 25/07/2015 7:13:30.47 menar @ Work Desktop
user5 HTML-5190-7294 21/07/2015 11:23:03.42 menar@Work2-1
user6 005625-RMS 14/07/2015 14:22:17.37 menar@Work2-1
user7 008700-LAP 03/08/2015 12:31:35.12 menar@Work2-1
user8 004503-DSK 08/07/2015 9:10:30.71 menar@Work2-1
user9 010030-LAP 03/08/2015 11:32:34.53 menar@Work2-1
user10 ABBY-PC 03/08/2015 13:59:25.21 GLH Contractors Web Portal
user3 009899-IT 10/07/2015 18:07:49.17 PB Service Desktop
user3 MEMBER-PC 11/08/2015 19:37:49.94 RDP NC-BACKUP01
user11 005240-LAP 03/08/2015 11:43:23.43 menar@Work2-1
user12 008617-DSK 03/08/2015 8:44:32.54 menar@Work2-1
user13 HP81017347825 02/08/2015 16:07:48.10 menar@Work2-1
user4 009178-DSK 03/08/2015 9:07:03.91 menar@Work2-1
user4 009178-DSK 07/07/2015 10:58:33.74 menar Desktop with Acrobat
user4 009178-DSK 07/07/2015 10:59:33.74 epic
我需要回到用户那里,他们有Mencap@Work2-1和一个"桌面发布的应用程序",这是一组特定的应用程序,我期待回到这个用户列表
1stLineS
user1
user2
我没有得到预期的结果…
我已经尝试了以下查询并返回0
$sql = "SELECT *
FROM tbl_name
WHERE publishedapp LIKE '"%Menar@Work2-1%'"
AND publishedapp LIKE '"%Desktop%'"
AND publishedapp LIKE '"%RDP%'"
GROUP BY username
$sql = "SELECT DISTINCT username, publishedapp
FROM tbl_name
WHERE publishedapp = '"Menar@Work2-1'"
AND publishedapp NOT LIKE '"%Desktop%'"
AND publishedapp NOT LIKE '"%RDP%'"
GROUP BY username
HAVING COUNT(DISTINCT publishedapp) > 1
";
我已经尝试了更多的变化,但说实话,我不知道我在做什么了,我有点脑死亡
您需要having
子句和group by
:
SELECT username
FROM tbl_name
GROUP BY username
HAVING SUM(publishedapp LIKE '%Mencap@Work2-1%') > 0 AND
SUM(publishedapp LIKE '%Desktop%') AND
SUM(publishedapp LIKE '%RDP%');
这就解决了我的问题
SELECT DISTINCT username,
GROUP_CONCAT(DISTINCT publishedapp SEPARATOR '"<br>'") AS publishedapp
FROM tbl_name
WHERE publishedapp LIKE '"%Menar@Work2-1%'"
OR publishedapp LIKE '"%@%'"
OR publishedapp LIKE '"%Desktop%'"
OR publishedapp LIKE '"%RDP%'"
GROUP BY username
HAVING COUNT(DISTINCT publishedapp) > 1 AND
SUM(DISTINCT publishedapp LIKE '"%Menar@Work2-1%'") > 0
不仅返回用户列表,它还返回一个包含所使用应用程序列表的字符串,如
1stLineS
Menar@work2-1
Citrix Delivery Services Console
user1
Epic
Menar@Work2-1
Powerplan
user2
Menar@Work2-1
Epic
试试这个
SELECT *
FROM tbl_name
WHERE publishedapp REGEXP 'Mencap@work2-1|Desktop|RDP'
GROUP BY username;