SQL 按日期和金额排列 现在金额分组不了

Question

现在能安日期排列，就是金额不行要什么分组呢

SELECT DISTINCT 
                      CONVERT(varchar(100), shou_dtime, 23) AS a1, SUM(CAST(shou_xj AS decimal)) AS a, SUM(CAST(shou_sm AS decimal)) AS b, SUM(CAST(shou_wx AS decimal)) AS c, 
                      SUM(CAST(shou_zfb AS decimal)) AS d, SUM(CAST(shou_sk AS decimal)) AS e, SUM(CAST(shou_fq AS decimal)) AS f, SUM(CAST(shou_tk AS decimal)) AS g, 
                      SUM(CAST(shou_hkxj AS decimal)) AS h, SUM(CAST(shou_hksm AS decimal)) AS i, SUM(CAST(shou_hkwx AS decimal)) AS j, SUM(CAST(shou_hkzfb AS decimal)) AS k, 
                      SUM(CAST(shou_hksk AS decimal)) AS l
FROM         tb_make_shoufei
WHERE     (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05')
GROUP BY shou_dtime
HAVING      (SUM(CAST(shou_xj AS decimal) + CAST(shou_sm AS decimal) + CAST(shou_wx AS decimal) + CAST(shou_zfb AS decimal) + CAST(shou_sk AS decimal) 
                      + CAST(shou_fq AS decimal) + CAST(shou_tk AS decimal) + CAST(shou_hkxj AS decimal) + CAST(shou_hksm AS decimal) + CAST(shou_hkwx AS decimal) 
                      + CAST(shou_hkzfb AS decimal) + CAST(shou_hksk AS decimal) + CAST(shou_hkfq AS decimal)) > 0)
ORDER BY a1

Answer 1

SELECT DISTINCT CONVERT(varchar(100), shou_dtime, 23) AS a1,
                          (SELECT     SUM(CAST(shou_xj AS decimal)) AS a
                            FROM          tb_make_shoufei AS a1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS a,
                          (SELECT     SUM(CAST(shou_sm AS decimal)) AS b
                            FROM          tb_make_shoufei AS b1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS b,
                          (SELECT     SUM(CAST(shou_xj AS decimal)) AS c
                            FROM          tb_make_shoufei AS a1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS c,
                          (SELECT     SUM(CAST(shou_sm AS decimal)) AS d
                            FROM          tb_make_shoufei AS b1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS d,
                          (SELECT     SUM(CAST(shou_xj AS decimal)) AS e
                            FROM          tb_make_shoufei AS a1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS e,
                          (SELECT     SUM(CAST(shou_sm AS decimal)) AS f
                            FROM          tb_make_shoufei AS b1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS f,
                          (SELECT     SUM(CAST(shou_xj AS decimal)) AS g
                            FROM          tb_make_shoufei AS a1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS g,
                          (SELECT     SUM(CAST(shou_sm AS decimal)) AS h
                            FROM          tb_make_shoufei AS b1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS h,
                          (SELECT     SUM(CAST(shou_xj AS decimal)) AS i
                            FROM          tb_make_shoufei AS a1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS i,
                          (SELECT     SUM(CAST(shou_sm AS decimal)) AS j
                            FROM          tb_make_shoufei AS b1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS j,
                          (SELECT     SUM(CAST(shou_xj AS decimal)) AS k
                            FROM          tb_make_shoufei AS a1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS k,
                          (SELECT     SUM(CAST(shou_sm AS decimal)) AS l
                            FROM          tb_make_shoufei AS b1
                            WHERE      (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05') AND (DAY(shou_dtime) = DAY(CONVERT(varchar(100), AA.shou_dtime, 23)))) AS l
FROM         tb_make_shoufei AS AA
WHERE     (YEAR(shou_dtime) = '2019') AND (MONTH(shou_dtime) = '05')
GROUP BY shou_dtime
ORDER BY a1

这样可以查出来，不过要5秒钟，查询代码也太长了，