一个表中,有固定的4个字段,大家看数据:字段1 字段2 字段3 字段4
A B C D
A A C F
B C D A
A E C D
B C A F
B C C F
我要求的结果为:
字段1 字段2 字段3 字段4 字段5
A B C D 1
A E C D 1
B C A F 2
B C C F 2
这四条记录就是我想要的:前两条记录的字段1、字段3、字段4的值都相同,我取出来;后两条记录的字段1、字段2、字段4的值都相同,我取出来。
也就是说,字段1、字段2、字段3、字段4中,其中任何3个字段值都相同的记录我都取出来,并且相同的分为一组。不晓得难度如何,觉得分不够,我再加就是哈,呵呵!
解决方案 »
- 将日期转换为字符串
- 请教!!!
- sql 计算两个时间间隔了几个工作日
- 请问这句有什么问题?select sid,okMoney from cost where (select ssid,sum(smoney) from cost_sales group by ssid)>1000
- 菜鸟数据库问题
- 关于 for xml path的sql怎么写?
- 安装sql sp4补丁时出问题了
- 一到面试提谁能帮我解答一下呀!!~
- sql server2000l 连不上:ping是通的,就是显示“拒绝访问”,或“没与服务器建立可信连接”,帮忙出主意呀?
- 用SQL命令怎么创建一个ACCESS的表(其中有一个字段是“自动编码”)
- SQL Server 2008安装文件中,jdbc驱动放置在哪里
- 帮忙看看这个存储过程的问题
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2012-10-29 08:50:27
-- blog : blog.csdn.net/herowang
---------------------------------
IF OBJECT_ID('[tc]') IS NOT NULL
DROP TABLE [tc]
go
CREATE TABLE [tc] (col1 VARCHAR(1),col2 VARCHAR(1),col3 VARCHAR(1),col4 VARCHAR(1))
INSERT INTO [tc]
SELECT 'A','B','C','D' UNION ALL
SELECT 'A','A','C','F' UNION ALL
SELECT 'B','C','D','A' UNION ALL
SELECT 'A','E','C','D' UNION ALL
SELECT 'B','C','A','F' UNION ALL
SELECT 'B','C','C','F'select * from [tc]
;with
wang as (select px=row_number() over (order by getdate()),* from tc),
wang2 as (select px,col=col1,colid=1 from wang union all
select px,col2,2 from wang union all
select px,col3,3 from wang union all
select px,col4,4 from wang ),
wang3
as(
select px1=s.px,px2=t.px from wang2 s join wang2 t on s.col =t.col and s.px<t.px and s.colid=t.colid
group by s.px,t.px
having count(*)>2)
select s.col1,s.col2,s.col3,s.col4 from wang3 join wang s on wang3.px1=s.px
union
select t.col1,t.col2,t.col3,t.col4 from wang3 join wang t on wang3.px1=t.px
union
select s.col1,s.col2,s.col3,s.col4 from wang3 join wang s on wang3.px2=s.px
union
select t.col1,t.col2,t.col3,t.col4 from wang3 join wang t on wang3.px2=t.px A B C D
A E C D
B C A F
B C C F
IF OBJECT_ID('[tc]') IS NOT NULL
DROP TABLE [tc]
go
CREATE TABLE [tc] (col1 VARCHAR(1),col2 VARCHAR(1),col3 VARCHAR(1),col4 VARCHAR(1))
INSERT INTO [tc]
SELECT 'A','B','C','D' UNION ALL
SELECT 'A','A','C','F' UNION ALL
SELECT 'B','C','D','A' UNION ALL
SELECT 'A','E','C','D' UNION ALL
SELECT 'B','C','A','F' UNION ALL
SELECT 'B','C','C','F'
;with
cte as (select id=row_number() over (order by getdate()),* from tc ) select a.col1,a.col2,a.col3,a.col4 from cte a, cte b
where a.id <> b.id
and case when a.col1 = b.col1 then 1 else 0 end
+ case when a.col2 = b.col2 then 1 else 0 end
+ case when a.col3 = b.col3 then 1 else 0 end
+ case when a.col4 = b.col4 then 1 else 0 end >= 3-- 结果
col1 col2 col3 col4
A E C D
A B C D
B C C F
B C A F
DROP TABLE [tc]
go
CREATE TABLE [tc] (col1 VARCHAR(1),col2 VARCHAR(1),col3 VARCHAR(1),col4 VARCHAR(1))
INSERT INTO [tc]
SELECT 'A','B','C','D' UNION ALL
SELECT 'A','A','C','F' UNION ALL
SELECT 'B','C','D','A' UNION ALL
SELECT 'A','E','C','D' UNION ALL
SELECT 'B','C','A','F' UNION ALL
SELECT 'B','C','C','F'
;with
cte as (select id=row_number() over (order by getdate()),* from tc ) select distinct a.col1,a.col2,a.col3,a.col4 from cte a, cte b
where a.id <> b.id
and case when a.col1 = b.col1 then 1 else 0 end
+ case when a.col2 = b.col2 then 1 else 0 end
+ case when a.col3 = b.col3 then 1 else 0 end
+ case when a.col4 = b.col4 then 1 else 0 end >= 3-- 结果
col1 col2 col3 col4
A A C F
A B C D
A E C D
B C A F
B C C F
B C D A
distinct * 都是4条