一段SQL,不执行...另求MSSQL入门教程
- SQL code
select pluno = a.pluno, pluname = a.pluname, spec = a.spec, pkunit = a.pkunit, cursprc = a.cursprc, slprc = a.slprc, moon1qty= m1.qty, moon2qty= m2.qty, moon3qty= m3.qty, moon4qty= m4.qty, moon5qty= m5.qty, moon6qty= m6.qty, moon7qty= m7.qty, moon8qty= m8.qty, moon9qty= m9.qty, moon10qty= m10.qty, moon11qty= m11.qty, moon12qty= m12.qty, salessum = m1.qty + m2.qty + m3.qty + m4.qty + m4.qty + m5.qty + m6.qty + m7.qty + m8.qty + m9.qty + m10.qty + m11.qty + m12.qty, saleamont= cursprc * salessum, maori = (m1.slnet+m2.slnet+m3.slnet+m4.slnet+m5.slnet+m6.slnet+m7.slnet+m8.slnet+m9.slnet+m10.slnet+m11.slnet+m12.slnet) - (curcprc * salessum), mgnrat = maori / (m1.slnet+m2.slnet+m3.slnet+m4.slnet+m5.slnet+m6.slnet+m7.slnet+m8.slnet+m9.slnet+m10.slnet+m11.slnet+m12.slnet) * 100, endqty = b.qty, endmont = b.qty * cursprc, endmoney = b.qty * slprc, zzl = saleamont / endmoneyfrom ( select pluno = max(a.pluno), pluname=a.pluname, spec = a.spec, pkunit = a.pkunit, curcsprc = b.curcsprc, slprc = b.slprc from basplumain a, baspluprc b where a.pluid = b.pluid group by a.pluid )a join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-01-01' AND DOCDAT <= '2011-01-31' group by b.pluid )m1 on a.pluid = m1.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-02-01' AND DOCDAT <= '2011-02-28' group by b.pluid )m2 on a.pluid = m2.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-03-01' AND DOCDAT <= '2011-03-31' group by b.pluid )m3 on a.pluid = m3.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-04-01' AND DOCDAT <= '2011-04-30' group by b.pluid )m4 on a.pluid = m4.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-05-01' AND DOCDAT <= '2011-05-31' group by b.pluid )m5 on a.pluid = m5.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-06-01' AND DOCDAT <= '2011-06-30' group by b.pluid )m6 on a.pluid = m6.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-07-01' AND DOCDAT <= '2011-07-31' group by b.pluid )m7 on a.pluid = m7.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-08-01' AND DOCDAT <= '2011-08-31' group by b.pluid )m8 on a.pluid = m8.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-09-01' AND DOCDAT <= '2011-09-31' group by b.pluid )m9 on a.pluid = m9.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-10-01' AND DOCDAT <= '2011-10-31' group by b.pluid )m10 on a.pluid = m10.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-11-01' AND DOCDAT <= '2011-11-30' group by b.pluid )m11 on a.pluid = m11.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-12-01' AND DOCDAT <= '2011-12-31' group by b.pluid )m12 on a.pluid = m12.pluid left join ( select qty = sum(begqty + inqty - outqty) from finstockshp where shpid = 2 group by pluid )b on b.pluid = a.pluid
语法检测通过了,可是在执行的时候,却提示...
- SQL code
服务器: 消息 207，级别 16，状态 3，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'pluid' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'cursprc' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'cursprc' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'salessum' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'curcprc' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'salessum' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'maori' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'cursprc' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'saleamont' 无效。服务器: 消息 207，级别 16，状态 1，行 1列名 'endmoney' 无效。
这些个字段都是实实在在在数据库中存在的啊....
哎,好郁闷,请帮忙看看大概是哪里有问题?
另外,如果手上有电子版的 入门.提高等 SQL教程,还望分享
谢谢
[解决办法]
没有这个字段？你检查一下看有没有写对。
MSSQL2008技术内幕系列，四本，值得一看
[解决办法]
- SQL code
select pluno = a.pluno, pluname = a.pluname, spec = a.spec, pkunit = a.pkunit, cursprc = a.cursprc,---子表 a 中定义的是curcsprc = b.curcsprc slprc = a.slprc, moon1qty= m1.qty, moon2qty= m2.qty, moon3qty= m3.qty, moon4qty= m4.qty, moon5qty= m5.qty, moon6qty= m6.qty, moon7qty= m7.qty, moon8qty= m8.qty, moon9qty= m9.qty, moon10qty= m10.qty, moon11qty= m11.qty, moon12qty= m12.qty, salessum = m1.qty + m2.qty + m3.qty + m4.qty + m4.qty + m5.qty + m6.qty + m7.qty + m8.qty + m9.qty + m10.qty + m11.qty + m12.qty, saleamont= cursprc * salessum,-------子表 a 中定义的是curcsprc = b.curcsprc 还有这里的salessum 用 （m1.qty + m2.qty + m3.qty + m4.qty + m4.qty + m5.qty + m6.qty + m7.qty + m8.qty + m9.qty + m10.qty + m11.qty + m12.qty）替换 maori = (m1.slnet+m2.slnet+m3.slnet+m4.slnet+m5.slnet+m6.slnet+m7.slnet+m8.slnet+m9.slnet+m10.slnet+m11.slnet+m12.slnet) - (curcprc * salessum), mgnrat = maori / (m1.slnet+m2.slnet+m3.slnet+m4.slnet+m5.slnet+m6.slnet+m7.slnet+m8.slnet+m9.slnet+m10.slnet+m11.slnet+m12.slnet) * 100, --maori 用 (m1.slnet+m2.slnet+m3.slnet+m4.slnet+m5.slnet+m6.slnet+m7.slnet+m8.slnet+m9.slnet+m10.slnet+m11.slnet+m12.slnet) - (curcprc * salessum) 替换 。注意用实际存在的列替换 (curcprc * salessum) endqty = b.qty, endmont = b.qty * cursprc,---子表 a 中定义的是curcsprc = b.curcsprc endmoney = b.qty * slprc, zzl = saleamont / endmoney --saleamont 用 子表 中 curcsprc 加 m1.qty + m2.qty + m3.qty + m4.qty + m4.qty + m5.qty + m6.qty + m7.qty + m8.qty + m9.qty + m10.qty + m11.qty + m12.qty --endmoney 用 b.qty * slprc 替换from ( select pluno = max(a.pluno), pluname=a.pluname, spec = a.spec, pkunit = a.pkunit, curcsprc = b.curcsprc, slprc = b.slprc ,pluid-----------------------缺少 from basplumain a, baspluprc b where a.pluid = b.pluid group by a.pluid )a join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-01-01' AND DOCDAT <= '2011-01-31' group by b.pluid )m1 on a.pluid = m1.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-02-01' AND DOCDAT <= '2011-02-28' group by b.pluid )m2 on a.pluid = m2.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-03-01' AND DOCDAT <= '2011-03-31' group by b.pluid )m3 on a.pluid = m3.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-04-01' AND DOCDAT <= '2011-04-30' group by b.pluid )m4 on a.pluid = m4.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-05-01' AND DOCDAT <= '2011-05-31' group by b.pluid )m5 on a.pluid = m5.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-06-01' AND DOCDAT <= '2011-06-30' group by b.pluid )m6 on a.pluid = m6.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-07-01' AND DOCDAT <= '2011-07-31' group by b.pluid )m7 on a.pluid = m7.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-08-01' AND DOCDAT <= '2011-08-31' group by b.pluid )m8 on a.pluid = m8.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-09-01' AND DOCDAT <= '2011-09-31' group by b.pluid )m9 on a.pluid = m9.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-10-01' AND DOCDAT <= '2011-10-31' group by b.pluid )m10 on a.pluid = m10.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-11-01' AND DOCDAT <= '2011-11-30' group by b.pluid )m11 on a.pluid = m11.pluid left join ( select qty = sum(b.qty), slamt = sum(b.slamt), slnet = sum(b.slnet), disamt = sum(b.disamt) ,pluid-----------------------缺少 from doc21bf a, doc21dt b where a.docno = b.docno and DOCDAT >= '2011-12-01' AND DOCDAT <= '2011-12-31' group by b.pluid )m12 on a.pluid = m12.pluid left join ( select qty = sum(begqty + inqty - outqty) ,pluid-----------------------缺少 from finstockshp where shpid = 2 group by pluid )b on b.pluid = a.pluid列的别名不能直接在 SELECT中 参与其他列的计算