Я пытаюсь сделать sql-запрос, который поможет мне получить следующий результат:
----------------------------------------------------------------------------
| RowNum | email | point_1 | point_2 | total_point |
----------------------------------------------------------------------------
| 1 | [email protected] | 120 | 70 | 190 |
----------------------------------------------------------------------------
Оператор запроса Sql 1 (чтобы получить значение RowNum, email и point_1):
$sql = "
select *
from
(
select ROW_NUMBER() OVER (ORDER BY m.first_name) as **RowNum**,
ltrim(rtrim(m.email_addr)) AS **email**,
CAST(isnull(p.points_accumulated,'0') AS INT) AS **point_1**
FROM (select * from crm_member_list where coy_id='HSG' and mbr_id not in (select mbr_id from o2o_tmp_mbr_issues_exclude) ) m
left join (select * from crm_member_points where coy_id='HSG') p
on p.mbr_id = m.mbr_id
where m.email_addr = '[email protected]'
and m.date BETWEEN '2016-08-01 00:00:00' AND '2016-08-31 23:59:00'
)sub where RowNum>? and RowNum<? order by RowNum";
Оператор запроса Sql 2 (чтобы получить значение point_2):
$sql = "
select CAST(isnull(p.points_accumulated,'0') AS INT) AS **point_2**
FROM (select * from crm_member_list where coy_id='HSG' and mbr_id not in (select mbr_id from o2o_tmp_mbr_issues_exclude) ) m
left join (select * from crm_member_points where coy_id='HSG') p
on p.mbr_id = m.mbr_id
where m.email_addr = '[email protected]'
and m.date BETWEEN '2016-09-01 00:00:00' AND '2016-09-30 23:59:00'";
Я попытался объединить 2 оператора, показанные выше, чтобы получить результат, но я получаю сообщение об ошибке
«выполнить sql напрямую, без курсора».
Комбинированный код:
$sql = "
select *
from
(
(select ROW_NUMBER() OVER (ORDER BY m.first_name) as **RowNum**,
ltrim(rtrim(m.email_addr)) AS **email**,
CAST(isnull(p.points_accumulated,'0') AS INT) AS **point_1**
FROM (select * from crm_member_list where coy_id='HSG' and mbr_id not in (select mbr_id from o2o_tmp_mbr_issues_exclude) ) m
left join (select * from crm_member_points where coy_id='HSG') p
on p.mbr_id = m.mbr_id
where m.email_addr = '[email protected]'
and m.date BETWEEN '2016-08-01 00:00:00' AND '2016-08-31 23:59:00'),
(select CAST(isnull(p.points_accumulated,'0') AS INT) AS **point_2**
FROM (select * from crm_member_list where coy_id='HSG' and mbr_id not in (select mbr_id from o2o_tmp_mbr_issues_exclude) ) m
left join (select * from crm_member_points where coy_id='HSG') p
on p.mbr_id = m.mbr_id
where m.email_addr = '[email protected]'
and m.date BETWEEN '2016-09-01 00:00:00' AND '2016-09-30 23:59:00'
)
)sub where RowNum>? and RowNum<? order by RowNum";
Как мне объединить оба оператора запроса, чтобы получить результат, показанный выше? И как мне добавить столбец point_1 и point_2, чтобы получить столбец total_point?
заранее спасибо
row_number()
. Укажите правильную СУБД. - person fancyPants   schedule 03.08.2016