请教一道SQL的题

最近看了一些公司的面经，里面有些SQL 的题我都不太会，想请教大佬。

给了table, 让输出交易间隔10秒的用户。谢谢了

Id	customer_name	transaction-time
1	AAA	2017-01-01 10:10:15
2	BBB	2017-01-01 11:11:11
3	CCC	2017-01-01 12:12:12
4	AAA	2017-01-01 10:10:20
5	AAA	2017-01-01 10:10:30
6	CCC	2017-01-01 12:12:22

张望堃 发表于 2019-11-15 13:38
请问1=1是什么意思呀

没有意义，因为1=1 always true，只是写sql的一个best practice或者说trick，如果注释掉某个filter， 不用考虑落单的and。

比如注释掉第一个filter，直接在前面加 -- 就好了
select distinct a.customer_name
from customers a inner join customers b
where 1=1
-- and a.customer_name = b.customer_name
and a.transaction_time = b.transaction_time - 10

如果不加1=1，注释掉第一个filter后，第二个filter前面有个and，就会有syntax error，还得手动删掉and。等加回来这个filter，还得把and 加回来
select distinct a.customer_name
from customers a inner join customers b
where
-- a.customer_name = b.customer_name
and a.transaction_time = b.transaction_time - 10

select distinct a.customer_name --, a.transaction_time as first_transaction, b.transaction_time as second_transaction
from customers a inner join customers b
where 1=1
and a.customer_name = b.customer_name
and a.transaction_time = b.transaction_time - 10

第一个column就是customer_name, 后面两个column debug用，所以comment out了

xiaobeixin 发表于 2019-11-15 13:26
select distinct a.customer_name --, a.transaction_time as first_transaction, b.transaction_time as s ...

请问1=1是什么意思呀

xiaobeixin 发表于 2019-11-15 13:26
select distinct a.customer_name --, a.transaction_time as first_transaction, b.transaction_time as s ...

大神能直接po一下mysql怎么写的吗？还是有点不清楚，谢谢

学习了……………

I run this code :select distinct a.customer_name --, a.transaction_time as first_transaction, b.transaction_time as second_transaction
from customers a inner join customers b
where 1=1
and a.customer_name = b.customer_name
and a.transaction_time = b.transaction_time - 10

But look like not show any Customer_Nmae, why?

I modify code like below , and test run seem to work:
Select distinct a.CustomerName,
        a.transaction_time as first_transaction,
         b.transaction_time as second_transaction,
         a.transaction_time -b.transaction_time  as diff
from dbo.Customers a inner join dbo.Customers b
On 1=1
and a.customerName = b.CustomerName
where
a.transaction_time != b.transaction_time
and a.transaction_time -b.transaction_time ='1900-01-01 00:00:10.000'

Show 2 records:
CustomerName        first_transaction        second_transaction        diff
AAA        2017-01-01 10:10:30.000        2017-01-01 10:10:20.000        1900-01-01 00:00:10.000
CCC        2017-01-01 12:12:22.000        2017-01-01 12:12:12.000        1900-01-01 00:00:10.000

可以多刷刷leetcode上面的题，好多这种self join的题

I rewrite code, not use self join use LEAD window function, I think much better?:
Declare @TimeDiff int
Set @TimeDiff = 10;

With CTE_NextTime
As(
Select Id, CustomerName,transaction_time,
Lead(Transaction_time,1,0) over (partition by customerName order by transaction_time)
as NextTransTime
From dbo.Customers)
Select CustomerName, transaction_time,NextTransTime,
Datepart(second,NextTransTime - transaction_time) as Diff
From CTE_NextTime
Where Datepart(second,NextTransTime - transaction_time) = @TimeDiff