MySQL - 如何合并所有相似的行(只有 cols 为 NULL)

Question

有一个巨大的表格，其中包含来自不同来源的经过解析的经纪人名称。我想按名称标准合并所有数据以删除重复项并统一每个经纪人的相关信息源表“经纪人”的结构:

id|name|website|logo|year|origin|offices|employess|languages|phone|address|

56|AxiTrader|www.axitrader.com| empty |2010| UK |empty |120|English|06546584|empty|
36|AxiTrader|www.axitrader.com|logo.png|2010|empty|Head|empty|empty|empty|Ave 13|

我想得到的结果:

56|AxiTrader|www.axitrader.com|logo.png|2010|UK|Head|120|English|06546584|Ave 13|

因此，我想合并所有具有相同名称的空列的行。附言在当前表格中，我有大约 130 列，因此列出所有列并不是一个好的变体。

我试图做的(SQL 查询):

SELECT id, MAX(name), MAX(website), MAX(logo), MAX(year), MAX(origin), MAX(offices), MAX(employees),
MAX(languages),
MAX(phone),
MAX(address),
MAX(types),
MAX(platforms),
...........
FROM brokers
GROUP BY name

但结果，我选择了那些具有 MAX 值的行，但这些行仍然没有合并，只是没有重复的行。

已更新

表结构:

1   id      Primary int(11)
2   name    text
3   website text
4   logo    text
5   year    text
6   origin  text
7   offices text
8   employees   text
9   languages   text
10  phone   text
11  address text
12  types   text
13  platforms   text
14  mindeposit  text
15  minaccount  text
16  mintradesize    text
17  maxtradesize    text
18  bonus   text
19  payouts text
20  accounttypes    text
21  demo    text
22  demourl text
23  liveurl text
24  depositmethods  text
25  withdrawal  text
26  tradingmethods  text
27  assetinfo   text
28  numberassets    text
29  expiry  text
30  accountcurrency text
31  tradingcurrency text
32  usonly  text
33  regulated   text
34  regulatedtext   text
35  licensed    text
36  licensedtext    text
37  commissions text
38  commisionsstext text
39  fees    text
40  feestext    text
41  mobiletrading   text
42  tablettrading   text
43  scamhistory text
44  leverage    text
45  spread  text
46  overallscore    text
47  pros    text
48  cons    text
49  features    text
50  siterating  text
51  status  text
52  review  text
53  about   text
54  publiclisting   text
55  techsupport text
56  spreadbetting   text
57  binaryoptions   text
58  blockedcountries    text
59  acceptedcountries   text
60  avgspreadeurusd text
61  avgspreadgbpusd text
62  avgspreadgold   text
63  fractionalpip   text
64  demoexpiration  text
65  eacompitable    text
66  eaallowed   text
67  liquidityproviders  text
68  cashback    text
69  tradingsignals  text
70  freevps text
71  nativesupport   text
72  24hours text
73  centaccount text
74  miniaccount text
75  standardaccount text
76  vipaccount  text
77  ecnaccount  text
78  maxleverageforex    text
79  maxleveragecommodities  text
80  maxleverageindices  text
81  maxleveragecfd  text
82  stopout text
83  executiontype   text
84  availableindices    text
85  availablecommodities    text
86  availablecfd    text
87  bitcoin text
88  usdindex    text
89  limitorder  text
90  chartingpackage text
91  newsstreaming   text
92  marketorder text
93  stoporder   text
94  tradeoffcharts  text
95  rolloverfee text
96  newstrading text
97  personalaccmanager  text
98  livechat    text
99  smsnotifications    text
100 swapaccounts    text
101 segregatedaccounts  text
102 interestonmargin    text
103 managedaccounts text
104 pamm    text
105 fax text
106 email   text
107 platformtimezone    text
108 webtrading  text
109 autotrading text
110 trustedmanagment    text
111 affiliate   text
112 advisors    text
113 education   text
114 api text
115 oco text
116 hedging text
117 dailyanal   text
118 trailingstops   text
119 oneclicktrading text
120 contests    text
121 otherinstruments    text
122 decimals    text
123 scalping    text
124 screenshot1 text
125 screenshot2 text
126 screenshot3 text
127 resource    text
128 published   tinyint(1)

Answer 1

我的理解是您想基于 SELECT 查询创建一个新表。 SELECT 查询本身就是这样做的——选择并返回你想要的数据——它不会改变你的表。您需要明确地创建它:

CREATE TABLE merged_table
  AS (SELECT id, MAX(name) name, MAX(website) website, 
                 MAX(logo) logo, MAX(year) year 
                 .....
      FROM brokers
      GROUP BY name);

看这里:

http://sqlfiddle.com/#!9/d22afa/2