php - 合并两个查询的问题

Question

我在获取一些数据时遇到问题。我有 4 张 table
用户
user_profile
用户收入
tax_category

我需要选择适用于每个用户的税种。为此，我有两个问题

第一个选择用户的总收入和年龄，第二个选择用户适用的税种。但我无法将其转换为一个查询

SELECT userid,user_profile_gender,date_part('years',age(user_profile_dob))+1 AS userage,incomeresult.totalincome FROM user
LEFT JOIN user_profile ON user_profile_userid = user.userid 
INNER JOIN 
        (SELECT SUM(income_amount) totalincome, income_userid FROM user_income
        WHERE income_date BETWEEN '2012-03-30' AND '2014-03-30' 
        GROUP BY income_userid) AS incomeresult ON user.userid = incomeresult.income_userid     
WHERE user.status = 1

---

SELECT * FROM tax_category
WHERE 70 BETWEEN taxcategory_agefrom AND taxcategory_ageto AND taxcategory_gender=1 AND 12000 BETWEEN taxcategory_incomefrom AND taxcategory_incometo

在第二个查询中，70 应该是 userage 的值，1 来自 user_profile_gender 和 12000 来自incomeresult.totalincome。
是否可以创建这样的查询？我正在使用 PostgreSQL 8.4

Answer 1

我猜你可以使用 CTE 语法:

WITH A AS
(  
SELECT 
  userid,
  user_profile_gender,
  date_part('years',age(user_profile_dob))+1 AS userage,
  incomeresult.totalincome 
FROM 
  user
  LEFT JOIN user_profile ON user_profile_userid = user.userid 
  INNER JOIN 
        (SELECT SUM(income_amount) totalincome, income_userid FROM user_income
        WHERE income_date BETWEEN '2012-03-30' AND '2014-03-30' 
        GROUP BY income_userid) AS incomeresult ON user.userid = incomeresult.income_userid     
WHERE 
  user.status = 1
)
SELECT * 
FROM 
  tax_category
  INNER JOIN A 
WHERE A.userage BETWEEN taxcategory_agefrom AND taxcategory_ageto AND taxcategory_gender=1 AND A.totalincome BETWEEN taxcategory_incomefrom AND taxcategory_incometo