这条语句能写出来吗?(有难度)
表test的结果如下:
序号 字段1 字段2 字段3
1 a a 6
2 b c 2
3 b d 2
4 b e 2
5 b e 5
要求:
1.只要字段1和字段2的值都相同,就认为记录是相同的。
2.只要字段3的值相同,就认为记录是相同的。
根据上述表可知最终的结果是2条不同记录。
如果通过sql语句得到结果:2 ?
------解决方案--------------------select distinct 字段3 from test where 字段1 <> 字段2
------解决方案--------------------SELECT COUNT(id) AS Expr1
FROM TEST
WHERE (id IN
(SELECT MAX(id)
FROM TEST
GROUP BY 字段1, 字段2)) AND (id IN
(SELECT MAX(id)
FROM TEST
GROUP BY 字段C))
------解决方案--------------------SELECT COUNT(id) AS Expr1 FROM TEST WHERE (id IN (SELECT MAX(id) FROM TEST GROUP BY 字段1, 字段2)) AND (id IN (SELECT MAX(id) FROM TEST GROUP BY 字段3))
------解决方案--------------------楼上意思还必须要有个identity field?
关注一下
------解决方案--------------------1 a a 6
2 b c 2
3 b d 2
4 b e 2
5 a a 5
结果应为1
------解决方案--------------------select count(*) from
(
select * from (select 字段3 from t group by 字段3 having count(字段3)> 2) as a
union all
select count(*) from t where 字段1=字段2
) as a