同事问我一个问题,要我写几条sql。假设表temp的结构是这样的:
字段: id, name
有这些记录:
123, foo
123, bar
123, test
124, test
125 foo
126 bar
127 foo
127 test
128 foo
128 bar
128 test
128 else
现在要写4条sql,找到符合下面四个条件的id:
1. 只有name = foo
2. 只有name = bar
3. name = foo以及name = bar
4. name != foo以及name != bar
第1条开始写出了这样的sql:
SELECT Id FROM temp WHERE Id IN (SELECT Id FROM temp GROUP BY Id HAVING COUNT(*) = 1) AND Name = 'foo'
显然不对,比如127这个Id就漏了。不过,同事的要求是不需要找出全部记录,随便找出几个就行了,所以这个还能满足要求。
第3条想了一会,写出来了
SELECT T1.Id FROM ( SELECT Id FROM temp WHERE Name = 'foo' GROUP BY Id ) as T1 INNER JOIN ( SELECT Id FROM temp WHERE Name = 'bar' GROUP BY Id ) as T2 ON T1.Id = T2.Id
第4条想不出,查了资料,写法很巧妙
SELECT Id FROM ( SELECT Id, MAX(CASE WHEN Name IN ('foo', 'bar') THEN 1 ELSE 0 END) AS flag FROM temp GROUP BY Id ) as T WHERE T.flag = 0
回过头来,第1条可以这样写
SELECT Id FROM ( SELECT Id, MAX(CASE WHEN Name = 'foo' THEN 1 ELSE 0 END) AS flag FROM temp GROUP BY Id ) as T WHERE T.flag = 1
第2条就很容易了,把foo 换成bar就好了。这个sql还是有点难度的,有点意思,学习一下。
标签:有点,name,temp,意思,sql,foo,Id,SELECT From: https://www.cnblogs.com/badnumber/p/17555192.html