这些查询单独运行100%
query1
SELECT year_entered,
month_entered,
IFNULL(amount - @n, 0) amount_diff,
@n := amount current_amount
FROM
(SELECT YEAR(date_entered) year_entered,
MONTHNAME(date_entered) month_entered,
SUM(amount) amount
FROM opportunities
INNER JOIN opportunities_cstm
ON ( opportunities.id = opportunities_cstm.id_c)
WHERE
YEAR(opportunities.date_entered) >= YEAR(CURDATE())
AND (`opportunities`.`deleted` <> '1') AND
(opportunities_cstm.interest_level_c = 'Ultraspin'
OR
opportunities_cstm.interest_level_c = 'Mycelx')
GROUP BY YEAR(date_entered), MONTH(date_entered)) q,
(SELECT @n := NULL) n
查询2 SELECT year_entered,
month_entered,
IFNULL(id - @n, 0) id_diff,
@n := id current_id
FROM
(SELECT YEAR(date_entered) year_entered,
MONTHNAME(date_entered) month_entered,
COUNT(id) id
FROM opportunities
INNER JOIN opportunities_cstm
ON ( opportunities.id = opportunities_cstm.id_c)
WHERE
YEAR(opportunities.date_entered) >= YEAR(CURDATE())
AND (`opportunities`.`deleted` <> '1') AND
(opportunities_cstm.interest_level_c = 'Ultraspin'
OR
opportunities_cstm.interest_level_c = 'Mycelx')
GROUP BY YEAR(date_entered), MONTH(date_entered)) q,
(SELECT @n := NULL) n
ON year_entered = year_entered
我需要如何金额差异和计数差异在一个查询,想要用户jasper报告报告这一点,或者如果有人可以帮助php返回行?
只是不知道如何得到这两个查询,并返回它们相邻
试试这个:
SELECT year_entered,
month_entered,
query1.amount_diff,
query2.id_diff FROM
(SELECT year_entered,
month_entered,
IFNULL(amount - @n, 0) amount_diff,
@n := amount current_amount
FROM
(SELECT YEAR(date_entered) year_entered,
MONTHNAME(date_entered) month_entered,
SUM(amount) amount
FROM opportunities
INNER JOIN opportunities_cstm
ON ( opportunities.id = opportunities_cstm.id_c)
WHERE
YEAR(opportunities.date_entered) >= YEAR(CURDATE())
AND (`opportunities`.`deleted` <> '1') AND
(opportunities_cstm.interest_level_c = 'Ultraspin'
OR
opportunities_cstm.interest_level_c = 'Mycelx')
GROUP BY YEAR(date_entered), MONTH(date_entered)) q,
(SELECT @n := NULL) n) query1 JOIN (
SELECT year_entered,
month_entered,
IFNULL(id - @n, 0) id_diff,
@n := id current_id
FROM
(SELECT YEAR(date_entered) year_entered,
MONTHNAME(date_entered) month_entered,
COUNT(id) id
FROM opportunities
INNER JOIN opportunities_cstm
ON ( opportunities.id = opportunities_cstm.id_c)
WHERE
YEAR(opportunities.date_entered) >= YEAR(CURDATE())
AND (`opportunities`.`deleted` <> '1') AND
(opportunities_cstm.interest_level_c = 'Ultraspin'
OR
opportunities_cstm.interest_level_c = 'Mycelx')
GROUP BY YEAR(date_entered), MONTH(date_entered)) q,
(SELECT @n := NULL) n
ON year_entered = year_entered
) query2 ON query1.year_entered = query2.year_entered AND query1.month_entered = query2.month_entered
SELECT year_entered,
month_entered,
IFNULL(amount - @n, 0) amount_diff,
IFNULL(id - @a, 0) id_diff,
@n := amount current_amount,
@i := id current_id
FROM (SELECT YEAR(date_entered) year_entered,
MONTHNAME(date_entered) month_entered,
SUM(amount) amount,
COUNT(id) id
FROM opportunities
INNER JOIN opportunities_cstm
ON ( opportunities.id = opportunities_cstm.id_c)
WHERE YEAR(opportunities.date_entered) >= YEAR(CURDATE())
AND (`opportunities`.`deleted` <> '1')
AND (opportunities_cstm.interest_level_c = 'Ultraspin'
OR opportunities_cstm.interest_level_c = 'Mycelx')
GROUP BY YEAR(date_entered), MONTH(date_entered)) q,
(SELECT @n := NULL, @i := NULL) n