同享会报表（朱峰提）
所有查询条件的销售统计表
1 -- 这个是加了所有查询条件的销售统计表 2 SELECT 3 * 4 FROM 5 ( 6 SELECT 7 agent.City, 8 City.Name, 9 SUM ( 10 CASE 11 WHEN agent.Status = 2 THEN 12 1 13 ELSE 14 0 15 END 16 ) AS yxcount, 17 SUM ( 18 CASE 19 WHEN Agent.Status <> 2 THEN 20 1 21 ELSE 22 0 23 END 24 ) AS wxcount, 25 SUM ( 26 CASE 27 WHEN c1.AgentID IS NULL THEN 28 0 29 ELSE 30 1 31 END 32 ) AS hycount 33 FROM 34 Agent 35 JOIN City ON Agent.City = City.ID 36 AND City = 3 37 AND AreaID = 1 38 LEFT JOIN ( 39 SELECT DISTINCT 40 AgentID 41 FROM 42 Customer 43 WHERE 44 RecommendTime > '2016/7/4 0:00:00' 45 AND RecommendTime <= '2016/7/20 0:00:00' 46 ) AS c1 ON c1.AgentID = Agent.ID 47 WHERE 48 Agent.RegisterTime > '2016/7/4 0:00:00' 49 AND Agent.RegisterTime <= '2016/7/20 0:00:00' 50 GROUP BY 51 agent.City, 52 City.Name 53 ) AS BB 54 JOIN ( 55 SELECT 56 Item.CityID, 57 Item.Name AS ItemName, 58 Item.ID AS itemid, 59 SUM ( 60 CASE 61 WHEN FollowType = 100 THEN 62 1 63 ELSE 64 0 65 END 66 ) AS 无效推荐, 67 COUNT (DISTINCT A2.CustomerID) AS 有效推荐, 68 SUM ( 69 CASE 70 WHEN FollowType = 1 THEN 71 1 72 ELSE 73 0 74 END 75 ) AS 预约, 76 SUM ( 77 CASE 78 WHEN FollowType = 3 THEN 79 1 80 ELSE 81 0 82 END 83 ) AS 到访, 84 SUM ( 85 CASE 86 WHEN FollowType = 4 THEN 87 1 88 ELSE 89 0 90 END 91 ) AS 认筹, 92 SUM ( 93 CASE 94 WHEN FollowType = 4 THEN 95 Mny 96 ELSE 97 0 98 END 99 ) AS 认筹金额, 100 SUM ( 101 CASE 102 WHEN FollowType = 5 THEN 103 1 104 ELSE 105 0 106 END 107 ) AS 认购, 108 SUM ( 109 CASE 110 WHEN FollowType = 5 THEN 111 Mny 112 ELSE 113 0 114 END 115 ) AS 认购金额, 116 SUM ( 117 CASE 118 WHEN FollowType = 6 THEN 119 1 120 ELSE 121 0 122 END 123 ) AS 签约, 124 SUM ( 125 CASE 126 WHEN FollowType = 6 THEN 127 Mny 128 ELSE 129 0 130 END 131 ) AS 签约金额, 132 SUM ( 133 CASE 134 WHEN FollowType = 7 THEN 135 1 136 ELSE 137 0 138 END 139 ) AS 回款, 140 SUM ( 141 CASE 142 WHEN FollowType = 7 THEN 143 Mny 144 ELSE 145 0 146 END 147 ) AS 回款金额 148 FROM 149 Item 150 JOIN ( 151 SELECT DISTINCT 152 CustomerID, 153 follow.ItemID, 154 FollowType, 155 Mny 156 FROM 157 Follow 158 JOIN Customer ON Follow.CustomerID = Customer.ID 159 WHERE 160 FollowType = 1 161 AND follow.FollowDate > '2016/7/4 0:00:00' 162 AND follow.FollowDate <= '2016/7/20 0:00:00' 163 UNION ALL 164 SELECT DISTINCT 165 CustomerID, 166 follow.ItemID, 167 FollowType, 168 Mny 169 FROM 170 Follow 171 JOIN Customer ON Follow.CustomerID = Customer.ID 172 WHERE 173 FollowType = 3 174 AND follow.FollowDate > '2016/7/4 0:00:00' 175 AND follow.FollowDate <= '2016/7/20 0:00:00' 176 UNION ALL 177 SELECT DISTINCT 178 CustomerID, 179 follow.ItemID, 180 FollowType, 181 Mny 182 FROM 183 Follow 184 JOIN Customer ON Follow.CustomerID = Customer.ID 185 WHERE 186 FollowType = 4 187 AND [State] = 0 188 AND follow.FollowDate > '2016/7/4 0:00:00' 189 AND follow.FollowDate <= '2016/7/20 0:00:00' 190 UNION ALL 191 SELECT DISTINCT 192 CustomerID, 193 A1.ItemID, 194 FollowType, 195 Mny 196 FROM 197 Follow AS A1 198 JOIN Customer ON A1.CustomerID = Customer.ID 199 WHERE 200 FollowType = 5 201 AND [State] = 0 202 AND CustomerID NOT IN ( 203 SELECT 204 CustomerID 205 FROM 206 Follow AS B1 207 WHERE 208 B1.CustomerID = A1.CustomerID 209 AND B1.FollowType IN (6, 7) 210 ) 211 AND A1.FollowDate > '2016/7/4 0:00:00' 212 AND A1.FollowDate <= '2016/7/20 0:00:00' 213 UNION ALL 214 SELECT DISTINCT 215 CustomerID, 216 A1.ItemID, 217 FollowType, 218 Mny 219 FROM 220 Follow AS A1 221 JOIN Customer ON A1.CustomerID = Customer.ID 222 WHERE 223 FollowType = 6 224 AND [State] = 0 225 AND CustomerID NOT IN ( 226 SELECT 227 CustomerID 228 FROM 229 Follow AS B1 230 WHERE 231 B1.CustomerID = A1.CustomerID 232 AND B1.FollowType = 7 233 ) 234 AND A1.FollowDate > '2016/7/4 0:00:00' 235 AND A1.FollowDate <= '2016/7/20 0:00:00' 236 UNION ALL 237 SELECT DISTINCT 238 CustomerID, 239 follow.ItemID, 240 FollowType, 241 Mny 242 FROM 243 Follow 244 JOIN Customer ON Follow.CustomerID = Customer.ID 245 WHERE 246 FollowType = 7 247 AND [State] = 0 248 AND follow.FollowDate > '2016/7/4 0:00:00' 249 AND follow.FollowDate <= '2016/7/20 0:00:00' 250 UNION ALL 251 SELECT 252 0 AS CustomerID, 253 ItemID, 254 100 AS FollowType, 255 0 AS Mny 256 FROM 257 InvalidRecommend 258 WHERE 259 RecommendTime > '2016/7/4 0:00:00' 260 AND RecommendTime <= '2016/7/20 0:00:00' 261 UNION ALL 262 SELECT 263 ID, 264 ItemID, 265 1001 AS FollowType, 266 0 AS Mny 267 FROM 268 Customer 269 WHERE 270 id NOT IN (SELECT CustomerID FROM Follow) 271 AND Customer.CityID = 3 272 AND Customer.RecommendTime > '2016/7/4 0:00:00' 273 AND Customer.RecommendTime <= '2016/7/20 0:00:00' 274 ) AS A2 ON Item.ID = a2.ItemID 275 GROUP BY 276 Item.CityID, 277 Item.Name, 278 Item.ID 279 ) CC ON bb.City = CC.CityID 280 ORDER BY 281 city
所有查询条件的推荐分析表
1 -- 这个加了所有查询条件的推荐分析表 2 SELECT 3 * 4 FROM 5 ( 6 SELECT 7 agent.City, 8 City.Name, 9 SUM ( 10 CASE 11 WHEN agent.Status = 2 THEN 12 1 13 ELSE 14 0 15 END 16 ) AS yxcount, 17 SUM ( 18 CASE 19 WHEN Agent.Status <> 2 THEN 20 1 21 ELSE 22 0 23 END 24 ) AS wxcount, 25 SUM ( 26 CASE 27 WHEN c1.AgentID IS NULL THEN 28 0 29 ELSE 30 1 31 END 32 ) AS hycount 33 FROM 34 Agent 35 JOIN City ON Agent.City = City.ID 36 AND City = 3 37 AND AreaID = 1 38 LEFT JOIN ( 39 SELECT DISTINCT 40 AgentID 41 FROM 42 Customer 43 WHERE 44 RecommendTime > '2016/7/19 0:00:00' 45 AND RecommendTime <= '2016/7/22 0:00:00' 46 ) AS c1 ON c1.AgentID = Agent.ID 47 WHERE 48 Agent.RegisterTime > '2016/7/19 0:00:00' 49 AND Agent.RegisterTime <= '2016/7/22 0:00:00' 50 GROUP BY 51 agent.City, 52 City.Name 53 ) AS BB 54 JOIN ( 55 SELECT 56 Item.CityID, 57 Item.Name AS ItemName, 58 Item.ID AS itemid, 59 SUM ( 60 CASE 61 WHEN FollowType = 100 THEN 62 1 63 ELSE 64 0 65 END 66 ) AS 无效推荐, 67 COUNT (DISTINCT A2.CustomerID) AS 有效推荐, 68 SUM ( 69 CASE 70 WHEN FollowType = 1 THEN 71 1 72 ELSE 73 0 74 END 75 ) AS 预约, 76 SUM ( 77 CASE 78 WHEN FollowType = 3 THEN 79 1 80 ELSE 81 0 82 END 83 ) AS 到访, 84 SUM ( 85 CASE 86 WHEN FollowType = 4 THEN 87 1 88 ELSE 89 0 90 END 91 ) AS 认筹, 92 SUM ( 93 CASE 94 WHEN FollowType = 4 THEN 95 Mny 96 ELSE 97 0 98 END 99 ) AS 认筹金额, 100 SUM ( 101 CASE 102 WHEN FollowType = 5 THEN 103 1 104 ELSE 105 0 106 END 107 ) AS 认购, 108 SUM ( 109 CASE 110 WHEN FollowType = 5 THEN 111 Mny 112 ELSE 113 0 114 END 115 ) AS 认购金额, 116 SUM ( 117 CASE 118 WHEN FollowType = 6 THEN 119 1 120 ELSE 121 0 122 END 123 ) AS 签约, 124 SUM ( 125 CASE 126 WHEN FollowType = 6 THEN 127 Mny 128 ELSE 129 0 130 END 131 ) AS 签约金额, 132 SUM ( 133 CASE 134 WHEN FollowType = 7 THEN 135 1 136 ELSE 137 0 138 END 139 ) AS 回款, 140 SUM ( 141 CASE 142 WHEN FollowType = 7 THEN 143 Mny 144 ELSE 145 0 146 END 147 ) AS 回款金额 148 FROM 149 Item 150 JOIN ( 151 SELECT DISTINCT 152 CustomerID, 153 follow.ItemID, 154 FollowType, 155 Mny 156 FROM 157 Follow 158 JOIN Customer ON Follow.CustomerID = Customer.ID 159 WHERE 160 FollowType = 1 161 AND follow.FollowDate > '2016/7/19 0:00:00' 162 AND follow.FollowDate <= '2016/7/22 0:00:00' 163 UNION ALL 164 SELECT DISTINCT 165 CustomerID, 166 follow.ItemID, 167 FollowType, 168 Mny 169 FROM 170 Follow 171 JOIN Customer ON Follow.CustomerID = Customer.ID 172 AND Customer.RecommendTime > '2016/7/19 0:00:00' 173 AND Customer.RecommendTime <= '2016/7/22 0:00:00' 174 WHERE 175 FollowType = 3 176 AND follow.FollowDate > '2016/7/4 0:00:00' 177 AND follow.FollowDate <= '2016/7/30 0:00:00' 178 UNION ALL 179 SELECT DISTINCT 180 CustomerID, 181 follow.ItemID, 182 FollowType, 183 Mny 184 FROM 185 Follow 186 JOIN Customer ON Follow.CustomerID = Customer.ID 187 AND Customer.RecommendTime > '2016/7/19 0:00:00' 188 AND Customer.RecommendTime <= '2016/7/22 0:00:00' 189 WHERE 190 FollowType = 4 191 AND [State] = 0 192 AND follow.FollowDate > '2016/7/4 0:00:00' 193 AND follow.FollowDate <= '2016/7/30 0:00:00' 194 UNION ALL 195 SELECT DISTINCT 196 CustomerID, 197 A1.ItemID, 198 FollowType, 199 Mny 200 FROM 201 Follow AS A1 202 JOIN Customer ON A1.CustomerID = Customer.ID 203 AND Customer.RecommendTime > '2016/7/19 0:00:00' 204 AND Customer.RecommendTime <= '2016/7/22 0:00:00' 205 WHERE 206 FollowType = 5 207 AND [State] = 0 208 AND CustomerID NOT IN ( 209 SELECT 210 CustomerID 211 FROM 212 Follow AS B1 213 WHERE 214 B1.CustomerID = A1.CustomerID 215 AND B1.FollowType IN (6, 7) 216 ) 217 AND A1.FollowDate > '2016/7/4 0:00:00' 218 AND A1.FollowDate <= '2016/7/30 0:00:00' 219 UNION ALL 220 SELECT DISTINCT 221 CustomerID, 222 A1.ItemID, 223 FollowType, 224 Mny 225 FROM 226 Follow AS A1 227 JOIN Customer ON A1.CustomerID = Customer.ID 228 AND Customer.RecommendTime > '2016/7/19 0:00:00' 229 AND Customer.RecommendTime <= '2016/7/22 0:00:00' 230 WHERE 231 FollowType = 6 232 AND [State] = 0 233 AND CustomerID NOT IN ( 234 SELECT 235 CustomerID 236 FROM 237 Follow AS B1 238 WHERE 239 B1.CustomerID = A1.CustomerID 240 AND B1.FollowType = 7 241 ) 242 AND A1.FollowDate > '2016/7/4 0:00:00' 243 AND A1.FollowDate <= '2016/7/30 0:00:00' 244 UNION ALL 245 SELECT DISTINCT 246 CustomerID, 247 follow.ItemID, 248 FollowType, 249 Mny 250 FROM 251 Follow 252 JOIN Customer ON Follow.CustomerID = Customer.ID 253 AND Customer.RecommendTime > '2016/7/19 0:00:00' 254 AND Customer.RecommendTime <= '2016/7/22 0:00:00' 255 WHERE 256 FollowType = 7 257 AND [State] = 0 258 AND follow.FollowDate > '2016/7/4 0:00:00' 259 AND follow.FollowDate <= '2016/7/30 0:00:00' 260 UNION ALL 261 SELECT 262 0 AS CustomerID, 263 ItemID, 264 100 AS FollowType, 265 0 AS Mny 266 FROM 267 InvalidRecommend 268 WHERE 269 RecommendTime > '2016/7/19 0:00:00' 270 AND RecommendTime <= '2016/7/22 0:00:00' 271 UNION ALL 272 SELECT 273 ID, 274 ItemID, 275 1001 AS FollowType, 276 0 AS Mny 277 FROM 278 Customer 279 WHERE 280 id NOT IN (SELECT CustomerID FROM Follow) 281 AND Customer.CityID = 3 282 AND Customer.RecommendTime > '2016/7/19 0:00:00' 283 AND Customer.RecommendTime <= '2016/7/22 0:00:00' 284 ) AS A2 ON Item.ID = a2.ItemID 285 GROUP BY 286 Item.CityID, 287 Item.Name, 288 Item.ID 289 ) CC ON bb.City = CC.CityID 290 ORDER BY 291 city
讨论
1 select a.Status,count(*) from Customer a where a.Deleted=0 2 GROUP BY a.Status; 3 4 select top 90 * from Follow; 5 6 select DISTINCT CustomerID,follow.ItemID from Customer 7 right join Follow on Customer.ID=Follow.CustomerID 8 where Follow.FollowType=7; 9 10 11 SELECT top 90 * from follow 12 13 14 SELECT DISTINCT CustomerID,follow.ItemID,FollowType, Mny 15 FROM 16 Follow 17 JOIN Customer ON Follow.CustomerID = Customer.ID 18 WHERE 19 FollowType = 7 20 AND [State] = 0
公众号请关注：侠之大者
浙公网安备 33010602011771号