运行脚本时TS报Duplicate Alias错误，SQL查询重复别名问题求助

Hey there, let's walk through the problems in your query that's causing the duplicate alias error, plus a few other syntax/logic issues that'll trip you up once you fix the alias problem.

First: The Duplicate Alias Root Cause #

You're joining the ts table three times without assigning unique aliases to each instance:

JOIN ts ON ts.cid = c.id 
JOIN ts ON ts.ptid = ps.id 
JOIN ts ON pcxref.id = ts.pid

The database can't tell these three ts references apart—they all default to using the table name as their alias, so it throws a duplicate alias error. You need to give each joined ts table a distinct, descriptive alias like ts_cust, ts_product, ts_xref to differentiate them.

Other Critical Issues to Fix #

Let's break down the other problems in your query that will cause errors even after fixing the alias issue:

• Invalid date condition: (tdate, 'YYYY-MM-DD') between '2020-01-01' AND '2020-10-09' isn't valid syntax. Depending on your database, use a date function to cast or truncate tdate to a date value. For example, in PostgreSQL you'd use DATE_TRUNC('day', ts.tdate), in MySQL DATE(ts.tdate).
• Unjoined table: You reference pcxref.id in your WHERE clause but never join the pcxref table to your query—this will throw an "unknown table" error.
• Empty IN clause: lower(tid) IN () is invalid SQL. Either remove this condition entirely, or populate it with actual values (e.g., lower(tid) IN ('tid001', 'tid002')).
• Invalid ORDER BY columns: You're ordering by c.e (but you selected c.email) and ts.td (but you selected ts.tdate). These will cause "unknown column" errors since those columns don't exist in your SELECT or table schema.
• Missing join for ps table: You reference ps.id in a join condition but never include a JOIN ps ON ... clause to link this table into your query.

Corrected Query Example #

Here's a revised version of your query with all these fixes applied (I used generic date handling and descriptive aliases to make the logic clear):

SELECT 
  c.email, 
  ts_cust.tid, 
  ts_cust.tdate, 
  ts_cust.sr, 
  ts_cust.c_sorigin, 
  ts_cust.subtype, 
  ts_cust.pid, 
  ps.c_msn, 
  ps.c_pgender 
FROM c
-- First join to ts for customer association (alias: ts_cust)
JOIN ts ts_cust ON ts_cust.cid = c.id
-- Join to ps table (assuming this links to ts via ptid)
JOIN ps ON ts_cust.ptid = ps.id
-- Join to pcxref table (since you referenced it in WHERE)
JOIN pcxref ON pcxref.id = ts_cust.pid
WHERE 
  DATE(ts_cust.tdate) BETWEEN '2020-01-01' AND '2020-10-09'
  AND l3 = '12345'
  -- Uncomment and add values if you need this condition
  -- AND lower(ts_cust.tid) IN ('sample-tid-1', 'sample-tid-2')
ORDER BY 
  c.email, 
  ts_cust.tid, 
  ts_cust.tdate DESC;

Key Takeaway #

Whenever you join the same table multiple times in a query, always assign unique aliases—this tells the database exactly which instance of the table you're referring to in SELECT, WHERE, and ORDER BY clauses.

内容的提问来源于stack exchange，提问作者domitsunami