sqlserver数据库
1、update语句用别名:
[color=red]得要这么用[/color]例如:
update bmd
set bmd.measure_money = bmd.measure_amount * (
select sb.unit_price
from sec_bill sb
where sb.sec_bill_id = bmd.sec_bill_id)
[color=red]from bill_measure_detail bmd[/color]
where bill_measure_id IN (
select distinct bill_measure_id
from bill_measure
where project_id = @projectId
and section_id = @sectionId
and (measure_date >= @startDate and
measure_date <= @endDate))
2、insert语句用group by:
insert into report_bill_num_vary
([color=red]before_vary_num,before_vary_money,
current_vary_num,current_vary_money
sec_bill_id,vary_type_id,vary_no[/color])
select before_vary_num,before_vary_price*before_vary_num,
after_vary_num-before_vary_num,sum(vary_money)
sec_bill_id,vary_type_id
from vary_detail
where vary_id = 11
group by [color=red]before_vary_num,before_vary_price*before_vary_num,
after_vary_num-before_vary_num,sec_bill_id,vary_type_id[/color]
select子句后面的所有字段,除了使用聚合函数的,都必须出现在group by子句后面
3、where和having区别
where是过滤分组前的数据,having是过滤分组后的数据(用到having,肯定用到group by)
1、update语句用别名:
[color=red]得要这么用[/color]例如:
update bmd
set bmd.measure_money = bmd.measure_amount * (
select sb.unit_price
from sec_bill sb
where sb.sec_bill_id = bmd.sec_bill_id)
[color=red]from bill_measure_detail bmd[/color]
where bill_measure_id IN (
select distinct bill_measure_id
from bill_measure
where project_id = @projectId
and section_id = @sectionId
and (measure_date >= @startDate and
measure_date <= @endDate))
2、insert语句用group by:
insert into report_bill_num_vary
([color=red]before_vary_num,before_vary_money,
current_vary_num,current_vary_money
sec_bill_id,vary_type_id,vary_no[/color])
select before_vary_num,before_vary_price*before_vary_num,
after_vary_num-before_vary_num,sum(vary_money)
sec_bill_id,vary_type_id
from vary_detail
where vary_id = 11
group by [color=red]before_vary_num,before_vary_price*before_vary_num,
after_vary_num-before_vary_num,sec_bill_id,vary_type_id[/color]
select子句后面的所有字段,除了使用聚合函数的,都必须出现在group by子句后面
3、where和having区别
where是过滤分组前的数据,having是过滤分组后的数据(用到having,肯定用到group by)