learning's questions

我看到了很多关于如何将行转换为列的示例，并且我能够做到这一点。这是具有以下内容的表：

Userid     action      date            TRACKING         zip        Subject
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        C        1/2/2020           Amz           30105       $1 Sales journey
cbg10        P        1/2/2020           Amz           30105       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        C        1/2/2020           Amz           45685       $1 Sales journey 
kel64        C        1/2/2020           Amz           45685       $1 Sales journey
kel64        p        1/2/2020           Amz           45685       $1 Sales journey
cbg10        O        4/18/2020          TG            30105       Summer arrives
cbg10        P        4/18/2020          TG            30105       Summer arrives
cbg10        C        4/18/2020          TG            30105       Summer arrives

这就是我希望表格的样子：正如您所看到的，有些打开的电子邮件打开了 2 次。而不是像第一个表中那样有 2 行，而是按主题添加人员采取的操作

Userid     date      O(Open)  C(Click)  P(Purchase)  TRACKING  zip     Subject
cbg10    1/2/2020     2         1        1            Amz     30105    $1 Sales journey
kel64    1/2/2020     3         2        1            Amz     45685    $1 Sales journey
cbg10    4/2/2020     1         1        1            Amz     30105    Summer arrives

This is the code that I have, but it is not giving me what I need
select UserId,

max(case when Aciton = 'O' then 1 else 0 end) as [O(Open)],
max(case when Aciton = 'P' then 1 else 0 end) as [P(Paid)],
max(case when Aciton = 'C' then 1 else 0 end) as [C(Cash)],
 [Date], 
 Tracking,
 Zip
 from myTable
 GROUP BY UserId, [Date], [Tracking], [Zip]
 ORDER BY UserId

I have 3 table A,B,C,D with the same columns and I'm trying to retrieve the 
user_id that are in table a and b but not c.

For instance:
Table A                       
user_id     closed acct
10102345     Yes
12456786     Yes

Table B             
user_id  closed acct  
10102345     Yes
12456786     Yes  

Table C
user_id  closed acct
45345696     No
45698788     Yes
12456786     Yes

这是我尝试但执行时间很长的代码：SELECT user_id FROM TableA WHERE user_id NOT IN (SELECT DISTINCT user_id
FROM TableB) OR user_id NOT IN (SELECT DISTINCT ID FROM Table C)

我有这个调查表，我正在尝试收集回答的用户 ID 的信息非常同意我正在尝试观察我的体重的问题，或者我喜欢喝黑咖啡，并且必须回答同意 或中立或强烈不同意 这些问题当最后期限临近时 ，我会茁壮成长，我只想尝试新事物。

UserId                QUESTION                               Answer
 1010            I am trying to watch my weight           Strongly Agree
 1120            I thrive when a deadline is close        Strongly Agree
 1120           I'm all about trying something new        Neutral
 2025            I'm all about trying something new       Agree
 3024            I love drinking Coffee Black             Strongly Disagree
 3024            I'm all about trying something new       Agree

我尝试编写的查询不断返回我没有编写的问题。这可能不是编写此类查询的最佳方式，但我正在学习如何正确编写这些查询。任何帮助，将不胜感激。

 SELECT userid_
  ,QUESTION 
  ,answer
   FROM [Survey_2019]]

Where QUESTION  = 'I'm trying to watch my weight'  AND
    Answer =  'Strongly agree'
  OR
      QUESTION    = 'I love drinking Coffee Black
  AND 
 Answer =  'Strongly agree'

AND 

 QUESTION= 'I thrive when a deadline is close'
  AND ANSWER= 'Agree' 
OR 'Neutral' OR 'Strongly Disagree'

 I have a large data set of over 10k+ rows and I'm trying to parse the url link
 that people of clicked on

 here is a table: dbo.email_list

 UserID   Cliked_Linked
 101012   https:// amz/profile_center?qp= 8eb6cbf33cfaf2bf0f51
 052469   htpps:// lago/center=age_gap=email_address=caipaingn4535=English_USA
 046894   https://itune/fr/unsub_email&utm=packing_345=campaign_6458_linkname=ghostrider

所以我尝试了这段代码：

UPDATE email_list set Clicked_Link= REVERSE(SUBSTRING(REVERSE(Cliked_Link),,CHARINDEX('.', REVERSE(ColumnName)) + 1, 999))

不幸的是，这没有用。

目标是让链接在“=”符号所在的位置拆分，并且等号之间的任何内容都在其自己的列中

This is the result I hope to have

UserID  COL_1                              COL_2                  COL_3                COL_4
101012  https:// amz/profile_center?qp    8eb6cbf33cfaf2bf0f51    NaN
052469  htpps:// lago/center              email_addres           caipaingn4535         English_USA
046894  https://itune/fr/unsub_email&utm  packing_345          campaign_6458_linknam   ghostrider

我有 2 张桌子。TableA是销售额，TableB是潜在客户表。我正在尝试查看潜在客户中的人是否TableB进行了购买，并且他们的名字是否出现在销售TableA中。如果他们这样做，我只想要出现在两张桌子上的人的名字。

表A（销售）

 customer   school            email     address    state    phone   Sales
 john doe   hight middle    [email protected]   111 updown   wi     2222222  $500 
 john doe   hight middle    [email protected]   111 updown   wi     2222222  $100 
 zow hi     hight middle    [email protected]  111 updown   wi     2222222  $200 
 calispo    dwight elem     [email protected] 34 jimm elis CA     35450456 $1,000 
 Jordan     clift high       [email protected]  1 unversity GA     11111111 $100 

表 B（导联）

 customer       school           email       address    state
 john doe      hight middle    [email protected]    111 updown     wi
 Kawi          hight middle     [email protected]     111 updown    wi
 calispo       dwight elem    [email protected]   34 jimm elis   CA

我想看到的：

 customer   school           email       address    state
 john doe   hight middle    [email protected]   111 updown   wi     2222222   $500
 alispo     dwight elem     [email protected] 34 jimm elis CA     35450456 $1,000

我尝试了这个查询，但它返回了我想要的信息的副本

 SELECT a.email as [sales_school]
  ,b.email[leadds_insti]
  ,a.*
  ,b.*
  FROM [dbo].[Sales] a 
  right JOIN leads b ON a.email= b.email

I have a table and I'm trying to remove all the duplicate and keep the  
the rows that has the latest datestamp.

Here is the table:

email address       orgin_date   new_opt_in_date   datestamp
[email protected]            1900-1-1     1900-1-1          2016-3-15
[email protected]            1900-1-1     1900-1-1          2016-3-15
[email protected]  2015-2-2     2016-12-26        2017-1-19
[email protected]  2015-2-2     2016-12-26        2018-6-6
[email protected]        2016-3-15    2016-3-151        2019-1-23
[email protected]        2016-3-15   2016-3-151         2018-5-6

I'm trying to keep only the data that has the recent datestamp, delete the 
rest and hope that the
output will like this:
email address       orgin_date   new_opt_in_date   datestamp
 [email protected]            1900-1-1     1900-1-1          2016-3-15
 [email protected]  2015-2-2     2016-12-26        2018-6-6
 [email protected]        2016-3-15    2016-3-151        2019-1-23

DELETE FROM `tablename` 
WHERE datestamp
NOT IN (
SELECT * FROM (
  SELECT MAX(datestamp) FROM tablename 
    GROUP BY emailaddress
 ) 
 )    
but nothing it didn't work     

我有 2 张桌子，我正在尝试加入并计算有多少人回答了每个问题。我正在尝试找到一种只有两列的更好方法。第一个是回答“只是我，我的配偶/伴侣”的人，另一列是回答儿童 0-5 岁、6-12 岁、13-17 岁的人。我正在尝试找出一种更好的方法来拥有一张看起来像这样的桌子

Question                  Answer               Children     Justme/partner
do you buy milk           weekly                400            20
do you consume cheese     daily                 15             300
how many time you drive   daily                 400            220  

这是 2 个表格 这是提供的称为“家庭”的表格：

User    JustMe      Children 0-5      Children 6-12    Myspouse/partner
  5      N              N               N                  N                           
  5      N              N               N                  N
  4      N              N               N                  N
  4      N              N               N                  N
 12      N              N               N                  N
 12     Just Me         N               N              Myspousepartner           
 46     Just Me         N        Children 6-12 years   Mypousepartner
 46     Just Me         N        Children 6-12 years   Mypousepartner

调查表

 User       questionid   answerid   question                   answer
  4          2             5         how often you buy gas     weekly
  46         3             4         how often you buy milk    monthly
  75         3             4         how often you buy milk     weekly
  13         4             2         how often you use coupon   monthly

我用

  SELECT id,question, Children_0_5_years, Children_6_12_years,  
  Children_13_17_years, count(*) AS NEW_COUNT
  FROM table1 inner join table2 on table1.user=table2.user group by...

不幸的是，这个查询返回了我不需要的东西。