求二个简单的 SQL语句

最新推荐文章于 2024-09-20 08:42:34 发布

zhaoxiaoyang5156

最新推荐文章于 2024-09-20 08:42:34 发布

阅读量3k

点赞数

本文链接：https://blog.csdn.net/zhaoxiaoyang5156/article/details/1725888

版权

问：

"Persons" 表:
编号      LastName      FirstName        Address          City
001       Hansen        Ola              Timoteivn 10     Sandnes
002       Svendson      Tove             Borgvn 23        Sandnes
003       Pettersen     Kari             Storgt 20        Stavanger

"Orders"表：
编号      Company      OrderNumber
001       Sega         3412
001       W3School     2312
001       Trio         4678
002       W3School     6798

问题一：
我用   select * from Persons where LastName='Hansen'
结果是：
编号      LastName      FirstName        Address          City
001       Hansen        Ola              Timoteivn 10     Sandnes
如果我想得到下面一样的结果：

编号      LastName      FirstName        Address          City
001       Hansen        Ola              Timoteivn 10     Sandnes
001       Hansen        Ola              Timoteivn 10     Sandnes
001       Hansen        Ola              Timoteivn 10     Sandnes
001       Hansen        Ola              Timoteivn 10     Sandnes
该用什么SQL语句，谢谢。

问题二：
我们看这二个表，不难发现，Persons和Orders表是一对多的关系
那么我想用SQL语句等到下面的结果，请问该如何写SQL语句

编号   Company                 OrderNumber
001    Sega，W3School，Trio    3412，2312，4678
002    W3School                6798
003
答：

fachun() ( 二级(初级)

) 信誉：100

2007-8-3 20:52:18

得分: 22

第一个问题：
select * from Persons where LastName='Hansen'
union all
select * from Persons where LastName='Hansen'
union all
select * from Persons where LastName='Hansen'
union all
select * from Persons where LastName='Hansen'
第二个问题：
用游标

paoluo(一天到晚游泳的鱼) ( 一星(中级)

) 信誉：100

2007-8-4 8:36:21

得分: 40

--創建測試環境
Create Table Persons
(编号 Char(3),
LastName Varchar(20),
FirstName Varchar(20),
Address Varchar(20),
City Varchar(20))
Insert Persons Select '001',       'Hansen',         'Ola',               'Timoteivn 10',     'Sandnes'
Union All Select '002',       'Svendson',      'Tove',            'Borgvn 23',         'Sandnes'
Union All Select '003',       'Pettersen',       'Kari',             'Storgt 20',            'Stavanger'

Create Table Orders
(编号 Char(3),
Company Varchar(20),
OrderNumber Int)
Insert Orders Select '001',       'Sega',             3412
Union All Select '001',       'W3School',     2312
Union All Select '001',       'Trio',              4678
Union All Select '002',       'W3School',     6798
GO
--測試
--问题一
select * from Persons where LastName='Hansen'
union all
select * from Persons where LastName='Hansen'
union all
select * from Persons where LastName='Hansen'
union all
select * from Persons where LastName='Hansen'
GO

--問題二
--創建函數
Create Function F_Get_Orders(@编号 Char(3), @Flag Bit)
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ''
If(@Flag = 0)
Select @S = @S + ',' + Company From Orders Where 编号 = @编号
Else
Select @S = @S + ',' + Cast(OrderNumber As Varchar(100)) From Orders Where 编号 = @编号
Select @S = Stuff(@S, 1, 1, '')
Return @S
End
GO
--調用
Select 编号, dbo.F_Get_Orders(编号, 0) As Company, dbo.F_Get_Orders(编号, 1) As OrderNumber From Persons
GO
--刪除測試環境
Drop Table Persons, Orders
Drop Function F_Get_Orders
GO
--結果
/*
--问题一結果
编号LastNameFirstNameAddressCity
001HansenOlaTimoteivn 10Sandnes
001HansenOlaTimoteivn 10Sandnes
001HansenOlaTimoteivn 10Sandnes
001HansenOlaTimoteivn 10Sandnes