求一句SQL语句！！

yes_no57111510 2009-09-14 04:18:55

表A数据如下：
id xmid(项目id)
1 a
1 b
1 c
2 a
3 b
1 d
2 b
3 c

表B数据如下：
id khid(考核id)
1 甲
2 甲
3 丙
4 甲
1 乙

查询出来的数据为：
1 a 甲
1 b null
1 c null
1 d null
2 a 甲
2 b null
3 b 丙
3 c null
4 null 甲

希望各位大侠帮忙！

...全文

155 20 打赏收藏转发到动态举报

写回复

用AI写文章

20 条回复

切换为时间正序

请发表友善的回复…

发表回复

yes_no57111510 2009-09-14

打赏
举报

[Quote=引用 18 楼 htl258 的回复:]
發現錯了，請問樓主如果有考核ID有丙或丁出現，那結果將如何顯示？
id khid(考核id)
1 甲
2 甲
3 丙
4 甲
1 乙
2 丙
3 丁

[/Quote]
那就输出：
1 a 甲
1 b null
1 c null
1 d null
2 a 甲
2 b 丙
3 b 丙
3 c 丁
4 null 甲

-晴天 2009-09-14

打赏
举报

create table tba(id int,xmid varchar(10))

insert into tba select 1,'a' 

insert into tba select 1,'b' 

insert into tba select 1,'c' 

insert into tba select 2,'a' 

insert into tba select 3,'b' 

insert into tba select 1,'d' 

insert into tba select 2,'b' 

insert into tba select 3,'c' 

create table tbb(id int,khid varchar(10))

insert into tbb select 1,'甲' 

insert into tbb select 2,'甲' 

insert into tbb select 3,'丙' 

insert into tbb select 4,'甲' 

insert into tbb select 1,'乙' 

go

select t2.id,t1.xmid,t2.khid from (

select * from tba a where xmid=(select top 1 xmid from tba where id=a.id)

)t1 full join (

select * from tbb b where khid=(select top 1 khid from tbb where id=b.id)

)t2

on t1.id=t2.id

go

drop table tba,tbb

/*

id          xmid       khid

----------- ---------- ----------

1           a          甲

2           a          甲

3           b          丙

4           NULL       甲

*/

htl258_Tony 2009-09-14

打赏
举报

發現錯了，請問樓主如果有考核ID有丙或丁出現，那結果將如何顯示？
id khid(考核id)
1 甲
2 甲
3 丙
4 甲
1 乙
2 丙
3 丁

yes_no57111510 2009-09-14

打赏
举报

多谢大家，我先去试一下！

htl258_Tony 2009-09-14

打赏
举报

/*---------------------------------

--  Author : htl258(Tony)

--  Date   : 2009-09-14 16:32:46

--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 

	Mar 29 2009 10:27:29 

	Copyright (c) 1988-2008 Microsoft Corporation

	Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)



---------------------------------*/

--> 生成测试数据表:a



IF NOT OBJECT_ID('[a]') IS NULL

	DROP TABLE [a]

GO

CREATE TABLE [a]([id] INT,[xmid] NVARCHAR(10))

INSERT [a]

SELECT 1,'a' UNION ALL

SELECT 1,'b' UNION ALL

SELECT 1,'c' UNION ALL

SELECT 2,'a' UNION ALL

SELECT 3,'b' UNION ALL

SELECT 1,'d' UNION ALL

SELECT 2,'b' UNION ALL

SELECT 3,'c'

GO

--SELECT * FROM [a]



--> 生成测试数据表:b



IF NOT OBJECT_ID('[b]') IS NULL

	DROP TABLE [b]

GO

CREATE TABLE [b]([id] INT,[khid] NVARCHAR(10))

INSERT [b]

SELECT 1,N'甲' UNION ALL

SELECT 2,N'甲' UNION ALL

SELECT 3,N'丙' UNION ALL

SELECT 4,N'甲' UNION ALL

SELECT 1,N'乙'

GO

--SELECT * FROM [b]



-->SQL查询如下:

;WITH t AS

(

  SELECT rn=ROW_NUMBER()OVER(PARTITION BY ISNULL(a.id,b.id) ORDER BY GETDATE()),

	ISNULL(a.id,b.id) ID,a.xmid,b.khid

  FROM a 

	FULL JOIN b

	  ON a.id=b.id

)

SELECT ID,xmid,CASE rn WHEN 1 THEN khid END khid 

FROM t

/*

ID          xmid       khid

----------- ---------- ----------

1           a          甲

1           a          NULL

1           b          NULL

1           b          NULL

1           c          NULL

1           c          NULL

1           d          NULL

1           d          NULL

2           b          甲

2           a          NULL

3           b          丙

3           c          NULL

4           NULL       甲



(13 行受影响)

*/

华夏小卒 2009-09-14

打赏
举报

如果1b是乙的话：



select isnull(a.id,b.id)as id,xmid,khid

from(

	select *,px=(select count(*) from @t where id=t.id and xmid<=t.xmid) from @t t

	)a

full join (

	select *,px=(select count(*) from @tb where id=t.id and khid<=t.khid) from @tb t

	) b on a.id=b.id and a.px=b.px

order by id,xmid

华夏小卒 2009-09-14

打赏
举报



declare @t table(id int,xmid nvarchar(20))

Insert @t select

1 ,   'a' union all select

1  ,  'b' union all select

1   , 'c' union all select

2    ,'a' union all select

3    ,'b' union all select

1    ,'d' union all select

2    ,'b' union all select

3    ,'c' 



declare @tb table(id int,khid nvarchar(20))

Insert @tb select 

1 ,   '甲' union all select

2  ,  '甲' union all select

3   , '丙' union all select

4    ,'甲' union all select

1    ,'乙' 



select isnull(a.id,b.id)as id,xmid,khid=case when isnull(a.px,b.px)=1 then khid else null end

from(

	select *,px=(select count(*) from @t where id=t.id and xmid<=t.xmid) from @t t

	)a

full join (

	select *,px=(select count(*) from @tb where id=t.id and khid<=t.khid) from @tb t

	) b on a.id=b.id and a.px=b.px

order by id,xmid



id          xmid                 khid

----------- -------------------- --------------------

1           a                    甲

1           b                    NULL

1           c                    NULL

1           d                    NULL

2           a                    甲

2           b                    NULL

3           b                    丙

3           c                    NULL

4           NULL                 甲



(9 行受影响)

--小F-- 2009-09-14

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2009-09-14 16:24:05

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

--

----------------------------------------------------------------

--> 测试数据：[a]

if object_id('[a]') is not null drop table [a]

go 

create table [a]([id] int,[xmid] varchar(1))

insert [a]

select 1,'a' union all

select 1,'b' union all

select 1,'c' union all

select 2,'a' union all

select 3,'b' union all

select 1,'d' union all

select 2,'b' union all

select 3,'c'

--> 测试数据：[b]

if object_id('[b]') is not null drop table [b]

go 

create table [b]([id] int,[khid] varchar(2))

insert [b]

select 1,'甲' union all

select 2,'甲' union all

select 3,'丙' union all

select 4,'甲' union all

select 1,'乙'

--------------开始查询--------------------------

select 

  isnull(t.id,b.id) as id ,a1.xmid,b.khid

from 

  a t

full join 

  (select id,min(khid) as khid from b group by id) as b

on 

  a1.id = b.id 

and 

  not exists (select 1 from a where id=t.id and xmid<t.xmid)

----------------结果----------------------------

/* (5 行受影响)

id          xmid khid

----------- ---- ----

1           a    甲

1           b    NULL

1           c    NULL

2           a    甲

3           b    丙

1           d    NULL

2           b    NULL

3           c    NULL

4           NULL 甲



(9 行受影响)



*/

昵称被占用了 2009-09-14

打赏
举报

[Quote=引用 4 楼 yes_no57111510 的回复:]
不好意思，写错了
查询结果应该是：
1 a 甲
1 b 乙
1 c null
1 d null
2 a 甲
2 b null
3 b 丙
3 c null
4 null 甲
[/Quote]

需要改下：

select 

  isnull(a1.id,b1.id) as id  

  ,a1.xmid

  ,b1.khid

from ( select *,row_number() over (partition by id order by xmid) as num from a) a1

full join (select *,row_number() over (partition by id order by khid) as num  from b ) as b1

on a1.id = b1.id anda1.num=b1.num

chuifengde 2009-09-14

打赏
举报

DECLARE @a TABLE(id INT,xmid char(1))

insert @a select 1    ,'a' 

union all select 1    ,'b' 

union all select 1    ,'c' 

union all select 2    ,'a' 

union all select 3    ,'b' 

union all select 1    ,'d' 

union all select 2    ,'b' 

union all select 3    ,'c' 

DECLARE @b TABLE(id INT, khid varchar(20))

insert @b select 1    ,'甲' 

union all select 2    ,'甲' 

union all select 3    ,'丙' 

union all select 4    ,'甲' 

union all select 1    ,'乙' 



SELECT ISNULL(a.id,b.id) id,xmid,khid FROM @a a

full JOIN   @b b 

ON a.id=b.id AND (SELECT COUNT(1)+1 FROM @a WHERE id=a.id AND xmid<a.xmid)=



(SELECT COUNT(1)+1 FROM @b WHERE id=b.id AND khid<b.khid)

ORDER BY a.id,xmid

--result

/*id          xmid khid                 

----------- ---- -------------------- 

1           a    甲

1           b    乙

1           c    NULL

1           d    NULL

2           a    甲

2           b    NULL

3           b    丙

3           c    NULL

4           NULL 甲

（所影响的行数为 9 行）

*/

guguda2008 2009-09-14

打赏
举报

[Quote=引用 8 楼 haiwer 的回复:]
SQL codeselectisnull(a1.id,b.id)as id
,a1.xmid
,b.khidfrom a a1fulljoin (select id,min(khid)as khidfrom bgroupby id)as bon a1.id= b.idandnotexists (select1from awhere id=a1.idand xmid<a1.xmid)

[/Quote]
海爷连错的都写出来了

guguda2008 2009-09-14

打赏
举报

SELECT A.*,B.khid

FROM A

LEFT JOIN B ON A.ID=B.ID

昵称被占用了 2009-09-14

打赏
举报

select 

  isnull(a1.id,b.id) as id  

  ,a1.xmid

  ,b.khid

from a a1

full join (select id,min(khid) as khid from b group by id) as b

on a1.id = b.id and not exists (select 1 from a where id=a1.id and xmid<a1.xmid)