【难】求一SQL查询语句
下面是创建测试数据的脚本，请copy到 SQL Query Analyzer中查看。
----------------------------------
use pubs
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[Orders] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [dbo].[Orders]
GO
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[Orders_Child1] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [dbo].[Orders_Child1]
GO
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] NOT NULL ,
[OrderCode] [varchar] (10) NOT NULL ,
[OrderCount] [int] NULL ,
[OrderRemark] [nvarchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Orders_Child1] (
[ChildID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[Date1] [smalldatetime] NULL ,
[Number1] [int] NULL ,
[Text1] [nvarchar] (20) NULL ,
[Text2] [nvarchar] (20) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders_Child1] WITH NOCHECK ADD
CONSTRAINT [PK_Orders_Child1] PRIMARY KEY CLUSTERED
(
[ChildID]
) ON [PRIMARY]
GO
insert into orders
select 1, 'A01 ',10000, 'A01文本备注 '
union select 2, 'A02 ',20000, 'A02文本备注 '
union select 3, 'A03 ',25000, 'A03文本内容 '
insert into orders_Child1
select 1,1, '2007-01-01 ',1000, 'A01的文本1 ', '齐 '
union select 2,1, '2007-01-02 ',2000, 'A01的文本2 ', '齐 '
union select 3,1, '2007-01-03 ',2000, 'A01的文本3 ', '齐 '
union select 4,2, '2007-01-04 ',100, 'A02的文本1 ', '不齐 '
union select 5,2, '2007-01-05 ',200, 'A02的文本2 ', '不齐 '
union select 6,2, '2007-01-06 ',200, NULL , '不齐 '
union select 7,3, '2007-01-07 ',10000, 'A03的文本1 ', '齐 '
union select 8,3, '2007-01-08 ',NULL , NULL , '不齐 '
----------------------------------
select * from Orders
select * from Orders_Child1
想得到的结果：
OrderID OrderCode OrderCount OrderRemark Date1 Number1 Text1 Text2
----------- ---------- ----------- ----------- ---------- ---------- ------------------------------- --------------------
1 A01 10000 A01文本备注 2007-01-03 5000 A01的文本1,A01的文本2,A01的文本3 齐
2 A02 20000 A02文本备注 2007-01-06 500 A02的文本1,A02的文本2 不齐
3 A03 25000 A03文本内容 2007-01-08 10000 A03的文本1 不齐
说明：
Date1: 取对应子行中的最大值
Number1: 对应子行求和
Text1： 对应子行文本串联叠加
Text2: 若所有对应子行文本内容相同，则显示相同值 '齐 '/ '不齐 '；
若有齐有不齐的，刚显示 '不齐 '；如果Text2不好做，先略过。
希望一个视图中实现，如果实在不行，再考滤存储过程中实现。
感觉一个视图中实现有点难度，视图我是没办法了，请教各位高手。
实际环境中类似于“Orders_Child1”的子表有很多个，需要把这些 1:N 的子表连接成跟主表“Orders”的行是 1:1 关系。
谢谢
[解决办法]
--完整代碼
use pubs
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[Orders] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [dbo].[Orders]
GO
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[Orders_Child1] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [dbo].[Orders_Child1]
GO
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] NOT NULL ,
[OrderCode] [varchar] (10) NOT NULL ,
[OrderCount] [int] NULL ,
[OrderRemark] [nvarchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Orders_Child1] (
[ChildID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[Date1] [smalldatetime] NULL ,
[Number1] [int] NULL ,
[Text1] [nvarchar] (20) NULL ,
[Text2] [nvarchar] (20) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders_Child1] WITH NOCHECK ADD
CONSTRAINT [PK_Orders_Child1] PRIMARY KEY CLUSTERED
(
[ChildID]
) ON [PRIMARY]
GO
insert into orders
select 1, 'A01 ',10000, N 'A01文本备注 '
union select 2, 'A02 ',20000, N 'A02文本备注 '
union select 3, 'A03 ',25000, N 'A03文本内容 '
insert into orders_Child1
select 1,1, '2007-01-01 ',1000,N 'A01的文本1 ',N '齐 '
union select 2,1, '2007-01-02 ',2000,N 'A01的文本2 ',N '齐 '
union select 3,1, '2007-01-03 ',2000,N 'A01的文本3 ',N '齐 '
union select 4,2, '2007-01-04 ',100, N 'A02的文本1 ',N '不齐 '
union select 5,2, '2007-01-05 ',200, N 'A02的文本2 ',N '不齐 '
union select 6,2, '2007-01-06 ',200, NULL ,N '不齐 '
union select 7,3, '2007-01-07 ',10000,N 'A03的文本1 ',N '齐 '
union select 8,3, '2007-01-08 ',NULL , NULL , N '不齐 '
GO
--建立一個合併的函數
Create Function F_GetText1(@OrderID Int)
Returns Nvarchar(1000)
As
Begin
Declare @S Varchar(1000)
Select @S = ' '
Select @S = @S + (Case IsNull(Text1, ' ') When ' ' Then ' ' Else ', ' + Text1 End) From Orders_Child1 Where OrderID = @OrderID Order By ChildID
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
--創建視圖
Create View V_TEST
As
Select
A.*,
Convert(Varchar(10), Max(B.Date1), 120) As Date1,
SUM(IsNull(B.Number1, 0)) As Number1,
dbo.F_GetText1(A.OrderID) As Text1,
Min(B.Text2) As Text2
From
Orders A
Left Join
Orders_Child1 B
On A.OrderID = B.OrderID
Group By
A.OrderID,
A.OrderCode,
A.OrderCount,
A.OrderRemark
GO
--測試
Select * From V_TEST
GO
--刪除測試環境
Drop Table Orders, Orders_Child1
Drop Function F_GetText1
Drop View V_TEST
--結果
/*
OrderIDOrderCodeOrderCountOrderRemarkDate1Number1Text1Text2
1A0110000A01文本备注2007-01-035000A01的文本1,A01的文本2,A01的文本3齐
2A0220000A02文本备注2007-01-06500A02的文本1,A02的文本2不齐
3A0325000A03文本内容2007-01-0810000A03的文本1不齐
*/
[解决办法]
写两个函数就可以了
create function get1(@a int) returns varchar(4)---得到书是齐还是不齐的函数
as
declare @b varchar(4)
begin
if exists(select * from Orders_Child1 a where a.OrderID=@a and a.Text2= '不齐 ')
set @b= '不齐 '
else
set @b= '齐 '
return @b
end
create function get2(@a int) returns varchar(1000)---得到text1列的合计
as
begin
declare @svar varchar(1000)
set @svar= ' '
select @svar=@svar+ ', '+a.text1 from orders_Child1 a where a.OrderID=@a order by a.ChildID
set @svar=stuff(@svar,1,1, ' ')
return @svar
end
最后写你要的语句
select a.orderid,a.OrderCode,sum(b.OrderCount),a.OrderRemark,max(b.data1),sum(b.num1),text1=dbo.get2(a.orderid),text2=dbo.get1(a.orderid) from Orders a join Orders_Child1 b on(a.orderid=b.orderid) group by a.orderid,a.OrderCode,a.OrderRemark
