写的存储过程只返回一行记录，不知应如何改正

[叮咚叮咚叮叮咚]

CREATE PROCEDURE spViewDayStatDetail
@workerid int,
@workday smalldatetime
AS

SELECT [dayStat_size].id,
([hwProduct].productname+' '+[hwProduct_size].sizename+' '+[gongxu].gongxuname) AS gongxu,
[dayStat_size].sizeNum,[dayStat_size].sumPay,
prodLevel=
Case prodLevel
WHEN 0 THEN '合格'
WHEN 1 THEN '不合格'
WHEN 2 THEN '劣质品'
End,
[dayStat_size].memo,[dayStat_size].shenhe
FROM [dayStat_size],[hwProduct_size],[hwProduct],[gongxu]
WHERE
[dayStat_size].dayStatid=(SELECT [dayStat].id FROM [dayStat] WHERE workerid=@workerid AND workday=@workday)
AND
[dayStat_size].sizeid=[hwProduct_size].id
AND
[hwProduct_size].parentid=[hwProduct].id
AND
[dayStat_size].gongxuid=[gongxu].id

GO

其中[hwProduct]与[hwProduct_size]为1对多关系
[daystat]与[dayStat_size]为1对多关系

还请大虾指教

[叮咚叮咚叮叮咚]

up

[bugchen888]

SELECT [dayStat_size].id,
([hwProduct].productname+' '+[hwProduct_size].sizename+' '+[gongxu].gongxuname) AS gongxu,
[dayStat_size].sizeNum,[dayStat_size].sumPay,
prodLevel=
Case prodLevel
WHEN 0 THEN '合格'
WHEN 1 THEN '不合格'
WHEN 2 THEN '劣质品'
End,
[dayStat_size].memo,[dayStat_size].shenhe
FROM [dayStat_size],[hwProduct_size],[hwProduct],[gongxu],[dayStat]
WHERE
[dayStat_size].dayStatid=[dayStat].id
AND
[dayStat].workerid=@workerid
AND
[dayStat].workday=@workday
AND
[dayStat_size].sizeid=[hwProduct_size].id
AND
[hwProduct_size].parentid=[hwProduct].id
AND
[dayStat_size].gongxuid=[gongxu].id

[叮咚叮咚叮叮咚]

不好意思，原来是插入到数据库中的数据有错误，导致只能取出一条记录

[filebat]

有数据没?顺便把表结构post出来吧.

[filebat]

这一句感觉有问题.
where
[dayStat_size].dayStatid=(SELECT [dayStat].id FROM [dayStat] WHERE workerid=@workerid AND workday=@workday)

[叮咚叮咚叮叮咚]

生成表
CREATE TABLE [hwproduct_size] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[parentid] [int] NULL ,
[sizename] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[xuhao] [int] NULL ,
CONSTRAINT [PK_hwproduct_size] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO


insert into [hwProduct_size](parentid,sizename,xuhao) values(3,'1 1/2""(DN40)',1)
insert into [hwProduct_size](parentid,sizename,xuhao) values(3,'1 1/2""(50)',2)
insert into [hwProduct_size](parentid,sizename,xuhao) values(3,'DN40',3)
insert into [hwProduct_size](parentid,sizename,xuhao) values(3,'DN100',4)
insert into [hwProduct_size](parentid,sizename,xuhao) values(4,'1 1/2""(DN40)',1)
insert into [hwProduct_size](parentid,sizename,xuhao) values(4,'2""(DN80)',2)

[叮咚叮咚叮叮咚]

CREATE TABLE [hwproduct] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[productname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[belongtomenuid] [int] NULL ,
[class1id] [int] NULL ,
[class2id] [int] NULL ,
[gongxu] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_hwproduct] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


----插入数据
insert into hwProduct(productname,belongtomenuid,class1id,class2id,gongxu) values('B2000L-YX',7,2,7,'1, 2, 3, 4')
insert into hwProduct(productname,belongtomenuid,class1id,class2id,gongxu) values('B2000L-YX',7,2,7,'1, 2, 3, 4')
insert into hwProduct(productname,belongtomenuid,class1id,class2id,gongxu) values('B2000L-YX',7,2,7,'1, 2, 3, 4')
insert into hwProduct(productname,belongtomenuid,class1id,class2id,gongxu) values('B2000L-YX',7,2,7,'1, 2, 3, 4')