表结构如下:
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'wages')
DROP TABLE wages
GO
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, 2)
INSERT wages VALUES(20.00, NULL, NULL, 3)
INSERT wages VALUES(30.00, NULL, NULL, 4)
INSERT wages VALUES(40.00, NULL, NULL, 5)
INSERT wages VALUES(NULL, 10000.00, NULL, 2)
INSERT wages VALUES(NULL, 20000.00, NULL, 3)
INSERT wages VALUES(NULL, 30000.00, NULL, 4)
INSERT wages VALUES(NULL, 40000.00, NULL, 5)
INSERT wages VALUES(NULL, NULL, 15000, 2)
INSERT wages VALUES(NULL, NULL, 25000, 3)
INSERT wages VALUES(NULL, NULL, 20000, 4)
INSERT wages VALUES(NULL, NULL, 14000, 5)
GO请问能得到如下结果吗?
10 10000 15000 2
20 20000 25000 3
30 30000 20000 4
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'wages')
DROP TABLE wages
GO
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, 2)
INSERT wages VALUES(20.00, NULL, NULL, 3)
INSERT wages VALUES(30.00, NULL, NULL, 4)
INSERT wages VALUES(40.00, NULL, NULL, 5)
INSERT wages VALUES(NULL, 10000.00, NULL, 2)
INSERT wages VALUES(NULL, 20000.00, NULL, 3)
INSERT wages VALUES(NULL, 30000.00, NULL, 4)
INSERT wages VALUES(NULL, 40000.00, NULL, 5)
INSERT wages VALUES(NULL, NULL, 15000, 2)
INSERT wages VALUES(NULL, NULL, 25000, 3)
INSERT wages VALUES(NULL, NULL, 20000, 4)
INSERT wages VALUES(NULL, NULL, 14000, 5)
GO请问能得到如下结果吗?
10 10000 15000 2
20 20000 25000 3
30 30000 20000 4
max(hourly_wage) as hourly_wage, --如果要累加,把max改成sum
max(salary) as salary, --如果要累加,把max改成sum
max(commission) as commission, --如果要累加,把max改成sum
num_sales
from wages
group by
num_sales
select ta0.hourly_wage ,
(select sum(ta1.salary) from wages as ta1 where ta1.num_sales = ta0.num_sales ) as salary ,
(select sum(ta2.commission) from wages as ta2 where ta2.num_sales = ta0.num_sales) as commission ,
ta0.num_sales
from wages as ta0 where ta0.hourly_wage is not null方法二:
select sum(hourly_wage ) as hourly_wage , sum(salary ) as salary , sum(commission) as commission ,num_sales
from wages group by num_sales单单从提问中的数据看来,数据之间是有规律的,所以,上面的两句才可以得到结果.
如果,数据不是规律的,则得不到这样的结果了.
2:你要的SQL没道理,至少你的数据库设计的有问题
3:你看以下是否是你需要的答案select t1.*,salary,commission
from (select emp_id-4*0 as emp_id,hourly_wage from tb1 where emp_id <=4) t1,
(select emp_id-4*1 as emp_id,salary from tb1 where emp_id >4 and <=8) t2,
(select emp_id-4*2 as emp_id,commission from tb1 where emp_id >8) t3
where t1.emp_id = t2.emp_id
and t1.emp_id = t3.emp_id
楼主,去www.mylinux.com.cn看看,那里专业资料多,应该能得到解答