我有关于SQL查询的问题 [英] I have a problem regarding an SQL query

问题描述

查询是

select SUM (p.Product_quan) +b.stock_on_hand  from Product_order as p
 inner join   b_ofc_stock as b on p.item_code =b.item_code
  where p.check_bit=1 and p.Product_name='T' group by b.stock_on_hand

我有两个包含许多列的表..在上面的查询中，我做了两个不同表的两个列的总和.现在我要o在表b_offc_stock中将此总和显示到stock_on_hand列...我该怎么办]

我的第一张桌子

I have two table which contain many of column.. in above query i done a sum of two column from two different table. now i want o display this sum to the column stock_on_hand from the table b_offc_stock... what should i do]

my first table

item_code	stock_on_hand	Product_name
P001	4	T
P002	3	P
P003	3	L

第二张桌子是

and second table is

Product_name    Product_quan    Delivery_date   Uname   P_id    Extend_date st_bit  flag    check_bit   item_code
1   T  1   2012-10-22 00:00:00.000 nitin123    16  2012-10-30  0   0   0   P001
2   P  2   2012-10-22 00:00:00.000 nitin123    17  NULL        0   0   1   P002
3   L  1   2012-10-25 00:00:00.000 nitin123    18  NULL        0   0   0   P003
4   P  3   2012-10-22 00:00:00.000 nupur123    19  NULL        0   0   1   P002
5   L  2   2012-10-26 00:00:00.000 nupur123    20  NULL        0   0   1   P003
6   L  2   2012-10-20 00:00:00.000 nitin123    21  2012-10-30  0   0   0   P003
7   T  3   2012-10-23 00:00:00.000 nupur123    22  NULL        0   0   1   P001

我希望表o/p是

i want the table o/p is

item_code	stock_on_hand	Product_name
P001	7	T
P002	11	P
P003	6	L

在此先感谢

Thanks in advance

推荐答案

以下表达式无效:

The following expression isn''t valid:

SUM (p.Product_quan) +b.stock_on_hand

将对所有记录执行总和，因此您不能将加法运算放在原来的位置.您需要将它们分开，如下所示:

The sum is executed for all records so you can''t put the addition where you did. You need to separate them, something like this:

select a.sum_of_quan + b.stock_on_hand from
  (select sum(quan) as sum_of_quan from Products) as a,
  (select stock_on_hand from Stock) as b;

祝你好运！

Good luck!

rashid，

您的查询是错误的..请进一步说明您到底需要什么..
如果您发布2个表的一些值，以及从表中需要什么结果，这将很有帮助.

因为如果给SUM()函数，它将添加列的所有字段，因此您不能期望它会提供所需的输出.查询结构是错误的..所以请提供有关您的错误的更多信息.

Hi rashid,

Ur query is wrong.. Please give more clarification aboout what you exactly need..
it will be helpfull if u post some values of 2 tables.. and what result you need from the tables..

Because if you give SUM() function it will add all the fields of your column, so that then you cant expect it to give a required output. query structure is wrong.. so please give more information about your error.

请尝试此操作

try this

create table items
(itemcode varchar(5),
stock_hand int,
product_Name varchar(2))


create table prod
(product_name varchar(2),
Productquan int,
checkbit bit,
itemcode varchar(5))

insert into items values ('P001',4,'T')
insert into items values ('P002',3,'P')
insert into items values ('P003',3,'L')

insert into prod values('T',1,0,'P001')
insert into prod values('P',2,1,'P002')
insert into prod values('L',1,0,'P003')
insert into prod values('P',3,1,'P002')
insert into prod values('L',2,1,'P003')
insert into prod values('L',2,0,'P003')
insert into prod values('T',3,1,'P001')

select p.itemcode,sum(stock_hand)+sum(productquan) from items i
inner join prod p on p.itemcode=i.itemcode 
where checkbit=1
group by p.itemcode

输出将是
itemcode(无列名)
P001 7
P002 11
P003 5

the output will be
itemcode (No column name)
P001 7
P002 11
P003 5

这篇关于我有关于SQL查询的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！