猜测, select m.* , n.num from t m, (select b,c,d,num = count(*) from t group by b,c,d) n where m.b = n.b and m.c = n.c and m.d = n.d或者 select * , num = (select count(*) from t where b = m.b and c = m.c and d = n.d) from t m
显示所有字段a b c d e f 都显示
SELECT b,c,d,COUNT(*) FROM t GROUP BY b,c,d
SELECT *,COUNT(*) FROM t where b = c and c = d GROUP BY b,c,d
select a,b,c,d,e,r, cnt= (select count(*) from t where b = m.b and c = m.c and d = n.d) from t m
select a.*,b.num from t a,(select b,c,d,count(*) as num from t group by b,c,d) b where a.b=b.b and a.c=b.c and a.d=b.d
表T(a,b,c,d,e,f,g,h,i,j,k) 要求把c d e 字段相同的输出一张表 把有两个相同的输出一张表 剩下的(两个以上)做一张表 输出所有字段
select m.产品名称 ,m.计价单位,m.方法,m.质量层次,count(*)as num from dbo.Sheet1$ as m , dbo.Sheet1$ as n where m.产品名称=n.产品名称 and m.计价单位=n.计价单位 and m.方法=n.方法 and m.质量层次=n.质量层次 group by m.产品名称 ,m.计价单位,m.方法,m.质量层次 having num=1 order by m.产品名称
我想用num控制它相同的个数 num=1 num=2 num>2 可我写的不对
--没有相同的 select a.*,b.num from @t a inner join (select b,c,d,count(1)as num from @t m where b<>c and b<>d and c<>D group by b,c,d)b on a.b=b.b and a.c=b.c and a.d=b.d --两个相同 select a.*,b.num from @t a inner join (select b,c,d,count(1)as num from @t m where (b=c and c<>d) or (b=d and c<>d) or (c=d and b<>d) group by b,c,d)b on a.b=b.b and a.c=b.c and a.d=b.d --三个相同 select a.*,b.num from @t a inner join (select b,c,d,count(1)as num from @t m where b=c and c=d group by b,c,d)b on a.b=b.b and a.c=b.c and a.d=b.d
表T(a,b,c,d,e,f,g,h,i,j,k) 要求把c d e 字段不相同的输出一张表 把有两个相同的输出一张表 剩下的(两个以上)做一张表 输出所有字段
你试试 select x.b,x.c,x.d,count(x.b) as row from t as x,t as y where x.b=y.b and x.c=y.c and x.d=y.d and(x.a<>y.a or x.e<>y.e or x.f<>y.f) group by x.b,x.c,x.d
select * from (select *,(select count(1) from @t m where m.b=n.b and m.c=n.c and m.d=n.d and m.e=n.e)as num from @t n) z where num=1 /* num=1时没有相同 num=2时有两个 num>2为两个以上不知道现在是否符合需求,看来我要增强理解能力了
猜测,
select m.* , n.num from t m,
(select b,c,d,num = count(*) from t group by b,c,d) n
where m.b = n.b and m.c = n.c and m.d = n.d或者
select * , num = (select count(*) from t where b = m.b and c = m.c and d = n.d) from t m
SELECT b,c,d,COUNT(*) FROM t GROUP BY b,c,d
select a,b,c,d,e,r,
cnt= (select count(*) from t where b = m.b and c = m.c and d = n.d)
from t m
where a.b=b.b
and a.c=b.c
and a.d=b.d
要求把c d e 字段相同的输出一张表
把有两个相同的输出一张表
剩下的(两个以上)做一张表
输出所有字段
select m.产品名称 ,m.计价单位,m.方法,m.质量层次,count(*)as num
from dbo.Sheet1$ as m , dbo.Sheet1$ as n
where m.产品名称=n.产品名称 and m.计价单位=n.计价单位
and m.方法=n.方法 and m.质量层次=n.质量层次
group by m.产品名称 ,m.计价单位,m.方法,m.质量层次
having num=1
order by m.产品名称
num=1
num=2
num>2
可我写的不对
--没有相同的
select a.*,b.num from @t a inner join
(select b,c,d,count(1)as num from @t m where b<>c and b<>d and c<>D group by b,c,d)b
on a.b=b.b and a.c=b.c and a.d=b.d
--两个相同
select a.*,b.num from @t a inner join
(select b,c,d,count(1)as num from @t m where (b=c and c<>d) or (b=d and c<>d) or (c=d and b<>d) group by b,c,d)b
on a.b=b.b and a.c=b.c and a.d=b.d
--三个相同
select a.*,b.num from @t a inner join
(select b,c,d,count(1)as num from @t m where b=c and c=d group by b,c,d)b
on a.b=b.b and a.c=b.c and a.d=b.d
要求把c d e 字段不相同的输出一张表
把有两个相同的输出一张表
剩下的(两个以上)做一张表
输出所有字段
select x.b,x.c,x.d,count(x.b) as row
from t as x,t as y
where x.b=y.b
and x.c=y.c
and x.d=y.d
and(x.a<>y.a or x.e<>y.e or x.f<>y.f)
group by x.b,x.c,x.d
select * from
(select *,(select count(1) from @t m where m.b=n.b and m.c=n.c and m.d=n.d and m.e=n.e)as num from @t n) z
where num=1
/*
num=1时没有相同
num=2时有两个
num>2为两个以上不知道现在是否符合需求,看来我要增强理解能力了