有两张表:A表记录所有代号的总数量,B表记录所有代号的出错数量,现求一个月内的每天的正确数量(若出错数量为空则为总数量,并要日期横向显示。这个问题已困扰我半天求高手指点
A:
qry_no(代号) sum_qty(总数量)
x 35
y 48
z 56B:
qry_no(代号) qry_date(出错日期) error_qty(出错数量)
x 1 5
x 3 15
y 2 10 想要的结果如下:
qry_no 1, 2, 3, 4, 5, 6, 7..........31(1-31日)
x 30 35 20 35 35 35 ...........35
y 48 38 48 48 .................48
z 56 56 56 56 .................56 不知道能否看明白
A:
qry_no(代号) sum_qty(总数量)
x 35
y 48
z 56B:
qry_no(代号) qry_date(出错日期) error_qty(出错数量)
x 1 5
x 3 15
y 2 10 想要的结果如下:
qry_no 1, 2, 3, 4, 5, 6, 7..........31(1-31日)
x 30 35 20 35 35 35 ...........35
y 48 38 48 48 .................48
z 56 56 56 56 .................56 不知道能否看明白
A.sum_qty-sum(if(qry_date=1,error_qty,0)) as `1`,
A.sum_qty-sum(if(qry_date=2,error_qty,0)) as `2`,
A.sum_qty-sum(if(qry_date=3,error_qty,0)) as `3`,
A.sum_qty-sum(if(qry_date=4,error_qty,0)) as `4`,
A.sum_qty-sum(if(qry_date=5,error_qty,0)) as `5`,
A.sum_qty-sum(if(qry_date=6,error_qty,0)) as `6`,
A.sum_qty-sum(if(qry_date=7,error_qty,0)) as `7`,
A.sum_qty-sum(if(qry_date=8,error_qty,0)) as `8`,
A.sum_qty-sum(if(qry_date=9,error_qty,0)) as `9`,
A.sum_qty-sum(if(qry_date=10,error_qty,0)) as `10`,
A.sum_qty-sum(if(qry_date=11,error_qty,0)) as `11`,
A.sum_qty-sum(if(qry_date=12,error_qty,0)) as `12`,
A.sum_qty-sum(if(qry_date=13,error_qty,0)) as `13`,
A.sum_qty-sum(if(qry_date=14,error_qty,0)) as `14`,
A.sum_qty-sum(if(qry_date=15,error_qty,0)) as `15`,
A.sum_qty-sum(if(qry_date=16,error_qty,0)) as `16`,
A.sum_qty-sum(if(qry_date=17,error_qty,0)) as `17`,
A.sum_qty-sum(if(qry_date=18,error_qty,0)) as `18`,
A.sum_qty-sum(if(qry_date=19,error_qty,0)) as `19`,
A.sum_qty-sum(if(qry_date=20,error_qty,0)) as `20`,
A.sum_qty-sum(if(qry_date=21,error_qty,0)) as `21`,
A.sum_qty-sum(if(qry_date=22,error_qty,0)) as `22`,
A.sum_qty-sum(if(qry_date=23,error_qty,0)) as `23`,
A.sum_qty-sum(if(qry_date=24,error_qty,0)) as `24`,
A.sum_qty-sum(if(qry_date=25,error_qty,0)) as `25`,
A.sum_qty-sum(if(qry_date=26,error_qty,0)) as `26`,
A.sum_qty-sum(if(qry_date=27,error_qty,0)) as `27`,
A.sum_qty-sum(if(qry_date=28,error_qty,0)) as `28`,
A.sum_qty-sum(if(qry_date=29,error_qty,0)) as `29`,
A.sum_qty-sum(if(qry_date=30,error_qty,0)) as `30`,
A.sum_qty-sum(if(qry_date=31,error_qty,0)) as `31`
from A inner jon B on A.qry_no=B.qry_no
group by A.qry_no
其实我想要的结果是 a left join b ,你的答案最接近,但是这个不是关键了。。