问一个sql的写法.
表aaa记录如下
a b c d e f g
A 1 2 3 4 5 6
B 1 2 3 4 5 6
B 6 5 4 3 2 1
C 1 2 3 4 5 6a b c d e f g是表的列名字.
A 1 2 3 4 5 6
B 1 2 3 4 5 6
B 6 5 4 3 2 1
C 1 2 3 4 5 6是表的四行记录数据现在我想select出a列不要重复的记录,就是如下结果A 1 2 3 4 5 6
B 1 2 3 4 5 6
C 1 2 3 4 5 6问下怎么写select语句呢?多谢了.
我下面这样写不行.select distinct(a),b,c,d,e,f,g from aaa 这样不行.
表aaa记录如下
a b c d e f g
A 1 2 3 4 5 6
B 1 2 3 4 5 6
B 6 5 4 3 2 1
C 1 2 3 4 5 6a b c d e f g是表的列名字.
A 1 2 3 4 5 6
B 1 2 3 4 5 6
B 6 5 4 3 2 1
C 1 2 3 4 5 6是表的四行记录数据现在我想select出a列不要重复的记录,就是如下结果A 1 2 3 4 5 6
B 1 2 3 4 5 6
C 1 2 3 4 5 6问下怎么写select语句呢?多谢了.
我下面这样写不行.select distinct(a),b,c,d,e,f,g from aaa 这样不行.
或
select a,b,c,d,e,f,g from aaa
group by a,b,c,d,e,f,g
select * from aaa t where not exists(select 1 from aaa where a=t.a and b<t.b)
insert into tb select 'B', 1 ,2 ,3 ,4, 5 ,6
insert into tb select 'B', 6, 5 ,4 ,3 ,2, 1
insert into tb select 'C', 1, 2, 3 ,4 ,5 ,6
select * from tb t where not exists(select 1 from tb where a=t.a and b<t.b)
drop table tb/*A 1 2 3 4 5 6
B 1 2 3 4 5 6
C 1 2 3 4 5 6*/
--先找出不重复的记录,在找符合条件的所有记录
那么我的 distinct 函数有什么作用的.
怎么不能实现我的结果呢?
DECLARE @t TABLE(a VARCHAR(64),b INT,c INT,d INT,e INT,f INT,g INT)
INSERT INTO @t
SELECT 'A',1,2,3,4,5,6
UNION ALL
SELECT 'B',1,2,3,4,5,6
UNION ALL
SELECT 'B',6,5,4,3,2,1
UNION ALL
SELECT 'C',1,2,3,4,5,6--
SELECT * FROM @t t1
WHERE t1.a in(SELECT a FROM @t t2 GROUP BY t2.a HAVING COUNT(*)=1)
UNION ALL
SELECT TOP 1 * FROM @t t1
WHERE t1.a in(SELECT a FROM @t t2 GROUP BY t2.a HAVING COUNT(*)<>1)
这样可以实现但是效率速度不怎么的好...呵呵
你那样用DISTINCT是对比整条记录的,不是单个字段的吧
with tb as (
select row_number()over(partition by a order by a)rowid,* from @t
)
select a,b,c,d,e,f,g from tb where rowid=12005可以这样
exists (select * from (select top 1 * from tb11 m where m.a=tb11.a) tb where tb.a=tb11.a and tb.b=tb11.b and tb.c=tb11.c and tb.d=tb11.d)