直接的语句是写不了的。需要先建立临时表SELECT Sell_no AS Sell_no,
convert(char(4),year(sell_Date))+'-'+convert(char(2),Month(sell_Date) AS yearmonth,
0 AS day,
0 AS money
INTO #Temp
FROM table1
GROUP convert(char(4),year(sell_Date))+'-'+convert(char(2),Month(sell_Date) , sell_no UPDATE #Temp
SET DAY = Day(Max(sell_date))
FROM table1
WHERE yearMonth = convert(char(4),year(sell_Date))+'-'+convert(char(2),Month(sell_Date)
UPDATE #Temp
SET Money = Money
FROM table1
WHERE yearMonth = convert(char(4),year(sell_Date))+'-'+convert(char(2),Month(sell_Date)
AND day = day(sell_Date)select * from #Temp
convert(char(4),year(sell_Date))+'-'+convert(char(2),Month(sell_Date) AS yearmonth,
0 AS day,
0 AS money
INTO #Temp
FROM table1
GROUP convert(char(4),year(sell_Date))+'-'+convert(char(2),Month(sell_Date) , sell_no UPDATE #Temp
SET DAY = Day(Max(sell_date))
FROM table1
WHERE yearMonth = convert(char(4),year(sell_Date))+'-'+convert(char(2),Month(sell_Date)
UPDATE #Temp
SET Money = Money
FROM table1
WHERE yearMonth = convert(char(4),year(sell_Date))+'-'+convert(char(2),Month(sell_Date)
AND day = day(sell_Date)select * from #Temp
解决方案 »
- float 转换为字符显示
- 关于 MS sql的一个关于动态sql语句的执行顺序问题!
- sql server系统视图sys.objects和sys.all_objects有什么区别?
- microsoft sql server中如何在已建好的表中导入数据
- 在分组中,想把两条记录合成一条记录要怎样写?
- BCB里面由程序传值给存储过程,操作结果显示在DBGrid里面?
- 求助
- 请问设定现有列的默认值的sql怎么写?
- SQL在后台运行的速度问题
- 没发现问题出在哪里了
- 请问用ado连接sql2000的详细语法是什么?
- 有一个页面上有三个空白需要填写(“姓名”“年龄”“学号”),其中至少要填一项,只用一个“提交”BUTTON,从一个表上查询,请问SQL语
year(sell_date) as 年,
month(sell_date) as 月,
day(sell_date) as 日,
sell_money as 金額
from table_name
where 日 in
(select max(day(sell_date)) from table_name group by sell_no ,year(sell_date), month(sell_date))
select sell_no,convert(char(7) ,sell_date,120) sell_month,max(datepart(day,sell_date)) sell_day into #tmp from a group by sell_no,convert(char(7),sell_date,120)select a.sell_no,#tmp.sell_month,#tmp.sell_day,a.sell_money from #tmp,a where #tmp.sell_no = a.sell_no and convert(char(7) ,sell_date,120) = #tmp.sell_month and #tmp.sell_day = datepart(day,a.sell_date)drop table #tmp
你的sell_date如果为char型:
SELECT *
FROM table1 as a
WHERE (NOT EXISTS
(SELECT * FROM table1 AS b
WHERE b.sell_no = a.sell_no
AND b.sell_date > a.sell_date
AND LEFT(b.sell_date, 7) = LEFT(a.sell_date, 7)
)
)
如果sell_date为datetime型,用cast()转化为上述格式的char型即可
你的sell_date如果为char型:
SELECT *
FROM table1 as a
WHERE (NOT EXISTS
(SELECT * FROM table1 AS b
WHERE b.sell_no = a.sell_no
AND b.sell_date > a.sell_date
AND LEFT(b.sell_date, 7) = LEFT(a.sell_date, 7)
)
)
如果sell_date为datetime型,用cast()转化为上述格式的char型即可
select sell_no,yr,max(cast(rr as int)),max(sell_money)
from ( select sell_no,
convert(char(7),sell_Date,121) as yr,
convert(char(2),sell_date,103) as rr,
sell_money
from temp1 ) b
group by b.sell_no ,b.yr