【难】求一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 关系。

谢谢

[paoluo]

--建立一個合併的函數
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.*,
Max(B.Date1) 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

[paoluo]

--完整代碼

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
--結果
/*
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 不齐
*/

[gj_zkb]

写两个函数就可以了
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

[健者天行]

好快的速度，
是的，已达到结果了。

实际坏境中会有很多的 Orders_Child1 表，有很多类似的的 Text1 字段，
也就是最好 dbo.F_GetText1 函数中的 'Text1'、'Orders_Child1' 是变动的。

按现在的方法则要每个字段建立一个合併的函數，将要建立很多很多呀，
大哥还有没有更好点的方法，
谢谢了。

[gj_zkb]

函数受限于SQL2000的限制只能针对某一个表,这个很难办到

[paoluo]

如果你的表名和字段名固定是那麼幾種的話，且不會很多的話，可以采用這種方法。

寫在一個函數中，根據不同的參數做判斷，取相應的字段和表的數據.

--建立一個合併的函數
Create Function F_GetText1(@Table Varchar(100), @Text Varchar(100), @OrderID Int)
Returns Nvarchar(1000)
As
Begin
Declare @S Varchar(1000)
Select @S = ''
If (@Table ='Orders_Child1' And @Text = 'Text1')
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('Orders_Child1', 'Text1', 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

[paoluo]

实际坏境中会有很多的 Orders_Child1 表，有很多类似的的 Text1 字段，

--------

如果你有很多Orders_Child1，那麼你的視圖就是變化的，一個Orders_Child1就需要建一個VIEW。

那麼最好一個Orders_Child1也可以建一個函數取得Text1 的值。

不過可以用我上面的方式，一個表取不同的字段可以寫在一個函數裡。

[健者天行]

Create Function F_GetText1(@Table Varchar(100), @Text Varchar(100), @OrderID Int)
Returns Nvarchar(1000)
As
Begin
Declare @sql varchar(2000)

set @sql ='
Declare @S Varchar(1000)
Select @S = ''''
Select @S = @S + (Case IsNull(Text1, '''') When '''' Then '''' Else '','' + '+@Text+' End) From '+@Table+' Where OrderID = '+ cast(@OrderID as varchar(10)) + 'Order By ChildID
Select @S = Stuff(@S, 1, 1, '''')
print @S'

execute(@sql) --能不能把execute放到函数里，并取得返回值？据我所知是不行了。

Return ''
End
GO

[paoluo]

函數不能使用execute。

[健者天行]

应该只能这样了，

谢谢

结贴。