SQL中的数据合并

Question

我尝试使用下面的查询来显示数据

select  e.Name,ic.Code,t1.pyear,t1.pmonth,t2.Amount     
from t1 
inner join t2 on t2.id=t1.id
inner join t3  on t3.Code=t2.Code
inner join t4 e on t4.employeeindex=t1.employeeindex 
where t1.pyear=2016 and t1.pmonth=1

union all 

select  e.Name,ic.Code,t1.pyear,t1.pmonth,t2.Amount     
from t1 
inner join t2 on t2.id=t1.id
inner join t3  on t3.Code=t2.Code
inner join t4 e on t4.employeeindex=t1.employeeindex 
where t1.pyear=2016 and t1.pmonth=2

我得到的数据是这样的-

scode amount month year       e_name
  abc     3847    1    2016    john
  ABC     20938   2    2016    john
  XYZ     2838    1    2016    david
  XYZ     29384   2    2016    david

现在我想显示这样的数据

 s_code  amount1 amount2  month1  month2   year  e_name
   abc     3847    20938    1       2       2016    john
   xyz     2838    29384    1       2       2016    david

有解决办法吗？

Answer 1

编辑

你可以试着用cte。下面只是一个例子，自己调整列名，因为它有点混乱

WITH unionall AS (
    select ic.Code, t2.Amount, t1.pmonth, t1.pyear, e.Name
    from t1 
    inner join t2 on t2.id=t1.id
    inner join t3  on t3.Code=t2.Code
    inner join t4 e on t4.employeeindex=t1.employeeindex 
    where t1.pyear=2016 and (t1.pmonth=1 OR t2.pmonth=2)
)

SELECT r1.scode, r1.amount as amount1, r2.amount as amount2
FROM unionall r1 inner join unionall r2 on r1.scode = r2.scode

？

如果您确信每个s_code都有2个月(或至少有一个花费的数字)，那么您可以轻松地对自己进行连接，并使用别名选择所有值，如下所示：

SELECT r1.scode, r1.amount as amount1, r2.amount as amount2 [...more fields...] FROM result r1 inner join result r2 on r1.scode = r2.scode [...many joins as many months...]

如果有多个月你不知道，我很可能会在mysql上做一个合并。基本上，您按scode分组，然后，在同一个字段中，有一个列表分隔的值。遗憾的是，您不能在group_concat中使用SQLServer，但是有关于web的解决方案指南。

sql服务器中的concat