我有两个表
Table A: Table B:
ID Name Name_id content
-------------- ----------------------------
1 ttt 1 math
2 aaa 1 english
3 ccc 1 physics
4 ddd 2 english
2 chinese
3 french 希望得到的结果
ID Name content
-------------------------------
1 ttt math
english
physics
2 aaa english
chinese
3 ccc french
4 ddd
请问各位大哥怎样能实现呢, 请指教
Table A: Table B:
ID Name Name_id content
-------------- ----------------------------
1 ttt 1 math
2 aaa 1 english
3 ccc 1 physics
4 ddd 2 english
2 chinese
3 french 希望得到的结果
ID Name content
-------------------------------
1 ttt math
english
physics
2 aaa english
chinese
3 ccc french
4 ddd
请问各位大哥怎样能实现呢, 请指教
解决方案 »
- 请各位高手指点一下我的SQL存储过程,谢谢啦....
- 有没有办法得到作业上一次正确运行的结果时间呢?
- 关于and / or 的复杂sql语句,向各位大虾请教
- 修改表结构后,怎样查出数据库中有哪些被引发为无效的存储过程和函数?
- 联接问题!
- 修改列的数据类型时出错,如果列为text类型,alter table mytable alter mycolumn int/float,如果列为int或float类型,alter table mytable
- 关于SQL触发器的问题
- 数据库中的列是个计算公式,如何插如数据
- 我想把下面的sql做到store procedure中,但不知道如何处理参数。数据库名为参数! 急!!
- SQLServer导入Oracle 后看不到数据?
- 在线急等。sql复制数据后更新
- MS-SQL中,怎么获取游标的记录总数
a.id,isnull(a.name,'') as [name],b.content
from
Table A a
full join
Table B b
where
a.id=b.Name_id
a.id,isnull(a.name,'') as [name],isnull(b.content,'') as content
from
Table A a
full join
Table B b
where
a.id=b.Name_id
declare @a table(id int,name varchar(5))
insert @a select
-------------- ----------------------------
1 , 'ttt'union all select
2 , 'aaa'union all select
3 , 'ccc'union all select
4 , 'ddd'
declare @b table(Name_id int, content varchar(15))
insert @b select
1 , 'math' union all select
1 , 'english' union all select
1 , 'physics'union all select
2 , 'english' union all select
2 , 'chinese' union all select
3 , 'french'
select id,name,content from @a a
left join @b b
on a.id=b.name_idid name content
----------- ----- ---------------
1 ttt math
1 ttt english
1 ttt physics
2 aaa english
2 aaa chinese
3 ccc french
4 ddd NULL(7 行受影响)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-08 17:19:25
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[TableA]
if object_id('[TableA]') is not null drop table [TableA]
go
create table [TableA]([ID] int,[Name] varchar(3))
insert [TableA]
select 1,'ttt' union all
select 2,'aaa' union all
select 3,'ccc' union all
select 4,'ddd'
--> 测试数据:[TableB]
if object_id('[TableB]') is not null drop table [TableB]
go
create table [TableB]([Name_id] int,[content] varchar(7))
insert [TableB]
select 1,'math' union all
select 1,'english' union all
select 1,'physics' union all
select 2,'english' union all
select 2,'chinese' union all
select 3,'french'
--------------开始查询--------------------------
select
a.id,isnull(a.name,'') as [name],isnull(b.content,'') as content
from
TableA a
full join
TableB b
on
a.id=b.Name_id
----------------结果----------------------------
/*id name content
----------- ---- -------
1 ttt math
1 ttt english
1 ttt physics
2 aaa english
2 aaa chinese
3 ccc french
4 ddd (7 行受影响)
*/
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-08 17:18:49
-------------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (ID INT,Name VARCHAR(3))
INSERT INTO @tb1
SELECT 1,'ttt' UNION ALL
SELECT 2,'aaa' UNION ALL
SELECT 3,'ccc' UNION ALL
SELECT 4,'ddd'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (Name_id INT,content VARCHAR(7))
INSERT INTO @tb2
SELECT 1,'math' UNION ALL
SELECT 1,'english' UNION ALL
SELECT 1,'physics' UNION ALL
SELECT 2,'english' UNION ALL
SELECT 2,'chinese' UNION ALL
SELECT 3,'french'--SQL查询如下:;WITH Liang AS
(
SELECT A.ID AS flag,A.Name,B.content,
rowid=ROW_NUMBER() OVER(PARTITION BY A.ID ORDER BY A.ID)
FROM @tb1 AS A
LEFT JOIN @tb2 AS B
ON A.ID = B.Name_id
)
SELECT
CASE WHEN rowid = 1 THEN RTRIM(flag) ELSE '' END AS ID,
CASE WHEN rowid = 1 THEN Name ELSE '' END AS Name,
ISNULL(content,'') AS Content
FROM Liang
ORDER BY flag,rowid;/*
ID Name Content
------------ ---- -------
1 ttt math
english
physics
2 aaa english
chinese
3 ccc french
4 ddd (7 row(s) affected)
*/
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-08 17:19:17
-- 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)---------------------------------*/
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([ID] int,[Name] nvarchar(3))
INSERT [a]
SELECT 1,N'ttt' UNION ALL
SELECT 2,N'aaa' UNION ALL
SELECT 3,N'ccc' UNION ALL
SELECT 4,N'ddd'
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([Name_id] int,[content] nvarchar(7))
INSERT [b]
SELECT 1,N'math' UNION ALL
SELECT 1,N'english' UNION ALL
SELECT 1,N'physics' UNION ALL
SELECT 2,N'english' UNION ALL
SELECT 2,N'chinese' UNION ALL
SELECT 3,N'french'
GO
--SELECT * FROM [b]-->SQL查询如下:
SELECT
ID=CASE WHEN b.[content]=(SELECT TOP 1 [content] FROM b WHERE name_id=a.id) THEN LTRIM(a.id) ELSE '' END,
Name=CASE WHEN b.[content]=(SELECT TOP 1 [content] FROM b WHERE name_id=a.id) THEN a.name ELSE '' END,
b.[content]
FROM a
JOIN b
ON a.id=b.name_id
/*
ID Name content
------------ ---- -------
1 ttt math
english
physics
2 aaa english
chinese
3 ccc french(6 行受影响)
*/
ID Name content
-------------------------------
1 ttt math
english
physics
2 aaa english
chinese
3 ccc french
4 ddd 请大家指教
create table a(id int,name varchar(20))
insert a
select 1,'ttt' union all
select 2,'aaa' union all
select 3,'ccc' union all
select 4,'ddd'
gocreate table b(name_id int,content varchar(20))
insert b
select 1,'math' union all
select 1,'english' union all
select 1,'physis' union all
select 2,'english' union all
select 2,'chinese' union all
select 3,'french'
go--drop table a,b
-- drop function dbo.f_strCREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(content as varchar)
FROM b
WHERE name_id=@col1
RETURN(STUFF(@re,1,1,''))
END
GO
select distinct a1.id,a1.name,dbo.f_str(b1.name_id)[content] from a a1
left join b b1
on a1.id = b1.name_id
id name content
----------- -------------------- ----------------------------------------------------------------------------------------------------
1 ttt math,english,physis
2 aaa english,chinese
3 ccc french
4 ddd NULL(所影响的行数为 4 行)
-- 建立测试数据
CREATE TABLE t1(id int IDENTITY(1,1), name varchar(3));
GOCREATE TABLE t2(name_id int, content varchar(50));
GOINSERT INTO t1(name) VALUES('ttt'),('aaa'),('ccc'),('ddd');
INSERT INTO t2(name_id, content) VALUES(1, 'math'),(1, 'english'),(1, 'physics'),
(2, 'english'),(2, 'chinese'),(3, 'french');-- 查询结果
SELECT t1.id, t1.name, ISNULL(t2.content,'') AS content FROM t1 LEFT JOIN t2 ON t1.id=t2.name_id;
/*
id name content
1 ttt math
1 ttt english
1 ttt physics
2 aaa english
2 aaa chinese
3 ccc french
4 ddd
*/
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-08 17:19:17
-- 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)---------------------------------*/
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([ID] int,[Name] nvarchar(3))
INSERT [a]
SELECT 1,N'ttt' UNION ALL
SELECT 2,N'aaa' UNION ALL
SELECT 3,N'ccc' UNION ALL
SELECT 4,N'ddd'
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([Name_id] int,[content] nvarchar(7))
INSERT [b]
SELECT 1,N'math' UNION ALL
SELECT 1,N'english' UNION ALL
SELECT 1,N'physics' UNION ALL
SELECT 2,N'english' UNION ALL
SELECT 2,N'chinese' UNION ALL
SELECT 3,N'french'
GO
--SELECT * FROM [b]-->SQL查询如下:
SELECT
ID=CASE WHEN ISNULL(b.[content],'')=ISNULL((SELECT TOP 1 [content] FROM b WHERE name_id=a.id),'') THEN LTRIM(a.id) ELSE '' END,
Name=CASE WHEN ISNULL(b.[content],'')=ISNULL((SELECT TOP 1 [content] FROM b WHERE name_id=a.id),'') THEN a.name ELSE '' END,
[content]=ISNULL(b.[content],'')
FROM a
LEFT JOIN b
ON a.id=b.name_id
/*
ID Name content
------------ ---- -------
1 ttt math
english
physics
2 aaa english
chinese
3 ccc french
4 ddd (7 行受影响)
*/MODIFY
-------------------------------
math
1 ttt english
physics
english
2 aaa chinese
3 ccc french
4 ddd
如果是多行会自动表A信息会自动下错一行
select id,name,content from @a a
left join @b b
on a.id=b.name_id
left join b on a.id = b.Name_id
试试!!!
declare @B table (name_id int,content nvarchar(10))
insert into @A select 1,'ttt'
union all select 2,'aaa'
union all select 3,'ccc'
union all select 4,'ddd'
insert into @B select 1,'math'
union all select 1,'english'
union all select 1,'physics'
union all select 2,'english'
union all select 2,'chinese'
union all select 3,'french'
;with china as
(
select *,px=ROW_NUMBER()over(order by name_id) from @B
),china1 as
(
select name_id= case when exists(select 1 from china where a.px>px and a.name_id=name_id)
then '' else name_id end,content
from china a
),china2 as
(select a.id,a.name,b.content from @A a full join china1 b on a.id=b.name_id
)select * from china2
id name content
----------- ---------- ----------
1 ttt math
NULL NULL english
NULL NULL physics
2 aaa english
NULL NULL chinese
3 ccc french
4 ddd NULL(7 行受影响)
declare @A table (id int,name varchar(10))
insert into @A (id,name)
select 1,'ttt'
union all select 2,'aaa'
union all select 3,'ccc'
union all select 4,'ddd'declare @B table (name_id int,content varchar(20))
insert into @B (name_id,content)
select 1,'math'
union all select 1,'english'
union all select 1,'physics'
union all select 2,'english'
union all select 2,'cheninese'
union all select 3,'french'select id=case when (select top 1 content from @b where name_id=a.id order by content)=b.content or b.content is null then convert(varchar,a.id) else '' end,
name=case when (select top 1 content from @b where name_id=a.id order by content)=b.content or b.content is null then a.name else '' end,
content=isnull(b.content,'')
from @a a left join @b b on a.id=b.name_id
order by a.id+1,b.content --如果不用a.id+1,排序会按id列排,不知是什么原因
if object_id('[TableA]') is not null drop table [TableA]
go
create table [TableA]([ID] int,[Name] varchar(3))
insert [TableA]
select 1,'ttt' union all
select 2,'aaa' union all
select 3,'ccc' union all
select 4,'ddd'
--> 测试数据:[TableB]
if object_id('[TableB]') is not null drop table [TableB]
go
create table [TableB]([Name_id] int,[content] varchar(7))
insert [TableB]
select 1,'math' union all
select 1,'english' union all
select 1,'physics' union all
select 2,'english' union all
select 2,'chinese' union all
select 3,'french';with romancezd
as(
select ROW=ROW_NUMBER ()over(PARTITION by a.id order by a.id),
a.ID,
isnull(a.Name,'') as name,
b.content from tablea a left join tableb b on a.ID=b.Name_id
) select id=case when row=1 then c.id else '' end,
name=case when ROW=1 then isnull(c.name,'') else '' end,
content=ISNULL(c.content,'')
from romancezd c