解决方案 »
- sql2000 怎样建立before 的触发器呢?
- 帮忙看看这个怎么优化
- 应该是比较困难的sql 语句, 游标?存储过程?
- 请教高手,现在出了一些问题
- 请问如何使得日期间的比较相等,如:'2004-09-08'与'2004-9-8'的比较为真!谢谢
- 要开发一个网络版生产管理系统,用户数30人左右,该如何来设计?
- profiler记录的远程调用查询与本地查询时间相差这么大?
- 标准差 与 标准差方 STDDEV(),VARIANCE()
- 请大家帮忙看看,这个SQL语句为什么会死循环?
- 排序问题:SQL_Latin1_General_CP1_CI_AS" and "Chinese_PRC_CI_AS"
- 求一树型节点显示的sql语句
- 各位大侠帮帮忙 一条mysql语句
SELECT de.AccountMoney
FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID left join CY_Bill on CY_Bill.BillID = de.BillID
WHERE CY_Bill.BillStatus = 1 And m.MethodName='现金'
select * from tb1 where id = (select id from tb2 where ...)
你的select id from tb2 where ...查询得到的结果又多条,而前面是等于号,这样就会出问题了,可以把这个等号改成“in”关键字就可以了。
FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID
WHERE CY_Bill.BillID = de.BillID and CY_Bill.BillStatus = 1 And m.MethodName='现金'你这条语句在FROM中没有CY_Bill,在WHERE子句中用CY_Bill.BillID……,肯定要报错的。
FROM CY_BillAccountDetail de
Left JOIN CD_AccountMethod m
ON de.AccountMethodID = m.MethodID
WHERE b.BillID = de.BillID
and b.BillStatus = 1 And m.MethodName='现金'替换为了select de.AccountMoney
from CY_Bill b,CY_BillAccountDetail de,CD_AccountMethod m where b.BillID=de.BillID
and b.BillStatus=1
and de.AccountMethodID = m.MethodID
and m.MethodName='现金'
and b.AccountMethodID =m.MethodID单独执行上面的代码可以成功在运用到整体的查询中还是会报出
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
会不会问题出在Convert转换过程中呢
Convert(numeric(16, 2),(select de.AccountMoney
from CY_Bill b,CY_BillAccountDetail de,CD_AccountMethod m where b.BillID=de.BillID and b.BillStatus=1
and de.AccountMethodID = m.MethodID
and m.MethodName='现金'
and b.AccountMethodID =m.MethodID)) as CashMoney
不太可能,这个只是把数字转换成numeric型,并且小数点后的数字个数为2而已。有没有提示是哪一行?这样不太好找,因为你的语句太长了。
SELECT de.AccountMoney
FROM CY_BillAccountDetail de
Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID
WHERE m.MethodName='现金' and
de.BillID in (select BillID from CY_Bill where BillStatus = 1)
SELECT de.AccountMoney
FROM CY_BillAccountDetail de
join CY_Bill b on b.BillID = de.BillID and b.BillStatus =1
Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID
WHERE m.MethodName='现金'
FROM CY_BillAccountDetail de
join CY_Bill b on b.BillID = de.BillID and b.BillStatus =1
Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID
WHERE m.MethodName='现金'
Convert(numeric(16, 2),b.TotalConsumeMoney) as TotalConsumeMoney, Convert(numeric(16, 2),b.FavouredMoney) as FavouredMoney,b.AvoidZeroMoney,Convert(numeric(16, 2),b.AccountReceivedMoney) as AccountReceivedMoney,
Convert(numeric(16, 2),b.PresentDishesMoney) as PresentDishesMoney,b.FeedbackMoney,b.InvoiceMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='现金')) as CashMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='支票')) as CheckMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='银行卡')) as CardMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID HERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='储值卡')) as VIPMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='招待')) as ServeMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.
AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='挂帐')) as TickMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='代金券')) as PresentMoney FROM CY_Bill b WHERE b.BillStatus = 1 AND AccountTime Between '2010-08-07 5:00:00' AND '2010-08-08 05:00') t;这样看来结构还是比较清晰了。应该是
Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='现金')) as CashMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='支票')) as CheckMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='银行卡')) as CardMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID HERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='储值卡')) as VIPMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='招待')) as ServeMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.
AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='挂帐')) as TickMoney,Convert(numeric(16, 2),(SELECT (de.AccountMoney) FROM CY_BillAccountDetail de Left JOIN CD_AccountMethod m ON de.AccountMethodID = m.MethodID WHERE b.BillID = de.BillID and b.BillStatus = 1 And m.MethodName='代金券')) as PresentMoney
这几条语句查询的结果不唯一了导致了“子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
”,可能是数据出了问题,可在每个子查询里面加上top1(也许业务不是这样)
前面一个问题“列前缀 'CY_Bill' 与查询中所用的表名或别名不匹配。
”暂时还没有看出来。最后一行(我贴出的代码)改成
FROM CY_Bill b WHERE b.BillStatus = 1 AND b.AccountTime Between '2010-08-07 5:00:00' AND '2010-08-08 05:00') t;
可能好一点。
再仔细检查一下吧!good luck!