Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выбор в таблице коэффициентов по номеру столбца без использования динамических запросов  [new]
sanitar
Member

Откуда:
Сообщений: 66
Есть таблица плотностей из методики по расчетам.
CREATE TABLE [dbo].[KT](
	[LPNL] [float] NOT NULL,
	[LPNH] [float] NOT NULL,
	[LPN] [int] NOT NULL,
	[T1] [float] NULL,
	[T2] [float] NULL,
	[T3] [float] NULL,
	[T4] [float] NULL,
	[T5] [float] NULL,
	[T6] [float] NULL,
	[T7] [float] NULL,
	[T8] [float] NULL,
	[T9] [float] NULL,
	[T10] [float] NULL,
	[T11] [float] NULL,
	[T12] [float] NULL,
	[T13] [float] NULL,
	[T14] [float] NULL,
	[T15] [float] NULL,
	[T16] [float] NULL,
	[T17] [float] NULL,
	[T18] [float] NULL,
	[T19] [float] NULL,
	[T20] [float] NULL,
 CONSTRAINT [PK_KT] PRIMARY KEY CLUSTERED 
(
	[LPN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into KT values (750, 759.9, 750, 1.082, 1.08, 1.078, 1.076, 1.073, 1.071, 1.068, 1.066, 1.063, 1.06, 1.057, 1.054, 1.051, 1.048, 1.045, 1.041, 1.038, 1.035, 1.032, 1.028) 
insert into KT values (760, 769.9, 760, 1.054, 1.052, 1.05, 1.048, 1.048, 1.043, 1.041, 1.038, 1.036, 1.033, 1.03, 1.027, 1.024, 1.021, 1.018, 1.015, 1.012, 1.009, 1.006, 1.003) 
insert into KT values (770, 779.9, 770, 1.027, 1.025, 1.023, 1.021, 1.019, 1.017, 1.014, 1.012, 1.009, 1.007, 1.004, 1.001, 0.999, 0.996, 0.993, 0.99, 0.987, 0.985, 0.982, 0.979) 
insert into KT values (780, 789.9, 780, 1.001, 0.999, 0.997, 0.995, 0.993, 0.991, 0.989, 0.987, 0.984, 0.982, 0.979, 0.977, 0.974, 0.971, 0.969, 0.966, 0.963, 0.961, 0.958, 0.955) 
insert into KT values (790, 799.9, 790, 0.976, 0.974, 0.972, 0.97, 0.969, 0.966, 0.964, 0.962, 0.96, 0.958, 0.955, 0.953, 0.95, 0.948, 0.945, 0.943, 0.94, 0.938, 0.935, 0.932) 
insert into KT values (800, 801.9, 800, 0.961, 0.96, 0.958, 0.956, 0.954, 0.952, 0.95, 0.948, 0.946, 0.943, 0.941, 0.939, 0.936, 0.934, 0.932, 0.929, 0.927, 0.924, 0.922, 0.919) 
insert into KT values (802, 803.9, 802, 0.956, 0.955, 0.953, 0.951, 0.949, 0.947, 0.945, 0.943, 0.941, 0.939, 0.937, 0.934, 0.932, 0.93, 0.927, 0.925, 0.922, 0.92, 0.917, 0.915) 
insert into KT values (804, 805.9, 804, 0.952, 0.95, 0.948, 0.947, 0.945, 0.943, 0.941, 0.939, 0.936, 0.934, 0.932, 0.93, 0.927, 0.925, 0.923, 0.92, 0.918, 0.915, 0.913, 0.91) 
insert into KT values (806, 807.9, 806, 0.947, 0.945, 0.944, 0.942, 0.94, 0.938, 0.936, 0.934, 0.932, 0.93, 0.928, 0.925, 0.923, 0.921, 0.918, 0.916, 0.913, 0.911, 0.909, 0.906) 
insert into KT values (808, 809.9, 808, 0.942, 0.941, 0.939, 0.937, 0.935, 0.933, 0.931, 0.929, 0.927, 0.925, 0.923, 0.921, 0.919, 0.916, 0.914, 0.911, 0.909, 0.907, 0.904, 0.902) 
insert into KT values (810, 811.9, 810, 0.938, 0.936, 0.934, 0.933, 0.931, 0.929, 0.927, 0.925, 0.923, 0.921, 0.919, 0.916, 0.914, 0.912, 0.909, 0.907, 0.905, 0.902, 0.9, 0.898) 
insert into KT values (812, 813.9, 812, 0.933, 0.931, 0.93, 0.928, 0.926, 0.924, 0.922, 0.92, 0.918, 0.916, 0.914, 0.912, 0.91, 0.907, 0.905, 0.903, 0.9, 0.898, 0.896, 0.893) 
insert into KT values (814, 815.9, 814, 0.928, 0.927, 0.925, 0.923, 0.922, 0.92, 0.918, 0.916, 0.914, 0.912, 0.91, 0.908, 0.905, 0.903, 0.901, 0.899, 0.896, 0.894, 0.892, 0.889) 
insert into KT values (816, 817.9, 816, 0.924, 0.922, 0.921, 0.919, 0.917, 0.915, 0.913, 0.912, 0.91, 0.907, 0.905, 0.903, 0.901, 0.899, 0.897, 0.894, 0.892, 0.89, 0.887, 0.885) 
insert into KT values (818, 819.9, 818, 0.919, 0.918, 0.916, 0.914, 0.913, 0.911, 0.909, 0.907, 0.905, 0.903, 0.901, 0.899, 0.897, 0.895, 0.892, 0.89, 0.888, 0.886, 0.883, 0.881) 
insert into KT values (820, 821.9, 820, 0.915, 0.913, 0.912, 0.91, 0.908, 0.907, 0.905, 0.903, 0.901, 0.899, 0.897, 0.895, 0.892, 0.89, 0.888, 0.886, 0.884, 0.881, 0.879, 0.877) 
insert into KT values (822, 823.9, 822, 0.91, 0.909, 0.907, 0.906, 0.904, 0.902, 0.9, 0.898, 0.896, 0.894, 0.892, 0.89, 0.888, 0.886, 0.884, 0.882, 0.879, 0.877, 0.875, 0.873) 
insert into KT values (824, 825.9, 824, 0.906, 0.904, 0.903, 0.901, 0.9, 0.898, 0.896, 0.894, 0.892, 0.89, 0.888, 0.886, 0.884, 0.882, 0.88, 0.878, 0.875, 0.873, 0.871, 0.869) 
insert into KT values (826, 827.9, 826, 0.902, 0.9, 0.899, 0.897, 0.895, 0.893, 0.892, 0.89, 0.888, 0.886, 0.884, 0.882, 0.88, 0.878, 0.876, 0.873, 0.871, 0.869, 0.867, 0.865) 
insert into KT values (828, 829.9, 828, 0.897, 0.896, 0.894, 0.893, 0.891, 0.889, 0.887, 0.886, 0.884, 0.882, 0.88, 0.878, 0.876, 0.874, 0.872, 0.869, 0.867, 0.865, 0.863, 0.861) 
insert into KT values (830, 831.9, 830, 0.893, 0.891, 0.89, 0.888, 0.887, 0.885, 0.883, 0.881, 0.879, 0.878, 0.876, 0.874, 0.872, 0.87, 0.867, 0.865, 0.863, 0.861, 0.859, 0.857) 
insert into KT values (832, 833.9, 832, 0.889, 0.887, 0.886, 0.884, 0.882, 0.881, 0.879, 0.877, 0.875, 0.873, 0.871, 0.87, 0.867, 0.865, 0.863, 0.861, 0.859, 0.857, 0.855, 0.853) 
insert into KT values (834, 835.9, 834, 0.884, 0.883, 0.881, 0.88, 0.878, 0.877, 0.875, 0.873, 0.871, 0.869, 0.867, 0.865, 0.863, 0.861, 0.859, 0.857, 0.855, 0.853, 0.851, 0.849) 
insert into KT values (836, 837.9, 836, 0.88, 0.879, 0.877, 0.876, 0.874, 0.872, 0.871, 0.869, 0.867, 0.865, 0.863, 0.861, 0.859, 0.857, 0.855, 0.853, 0.851, 0.849, 0.847, 0.845) 
insert into KT values (838, 839.9, 838, 0.876, 0.874, 0.873, 0.871, 0.87, 0.868, 0.867, 0.865, 0.863, 0.861, 0.859, 0.857, 0.855, 0.853, 0.851, 0.849, 0.847, 0.845, 0.843, 0.841) 
insert into KT values (840, 841.9, 840, 0.872, 0.87, 0.869, 0.867, 0.866, 0.864, 0.862, 0.861, 0.859, 0.857, 0.855, 0.853, 0.851, 0.849, 0.847, 0.845, 0.843, 0.841, 0.839, 0.837) 
insert into KT values (842, 843.9, 842, 0.867, 0.866, 0.865, 0.883, 0.862, 0.86, 0.858, 0.857, 0.855, 0.853, 0.851, 0.849, 0.847, 0.846, 0.844, 0.842, 0.84, 0.838, 0.836, 0.833) 
insert into KT values (844, 845.9, 844, 0.863, 0.862, 0.861, 0.859, 0.858, 0.856, 0.854, 0.853, 0.851, 0.849, 0.847, 0.845, 0.844, 0.842, 0.84, 0.838, 0.836, 0.834, 0.832, 0.83) 
insert into KT values (846, 847.9, 846, 0.859, 0.858, 0.857, 0.855, 0.854, 0.852, 0.85, 0.849, 0.847, 0.845, 0.843, 0.842, 0.84, 0.838, 0.836, 0.834, 0.832, 0.83, 0.828, 0.826) 
insert into KT values (848, 849.9, 848, 0.855, 0.854, 0.853, 0.851, 0.85, 0.848, 0.846, 0.845, 0.843, 0.841, 0.839, 0.838, 0.836, 0.834, 0.832, 0.83, 0.828, 0.826, 0.824, 0.822) 
insert into KT values (850, 851.9, 850, 0.851, 0.85, 0.849, 0.847, 0.846, 0.844, 0.842, 0.841, 0.839, 0.837, 0.836, 0.834, 0.832, 0.83, 0.828, 0.826, 0.824, 0.822, 0.82, 0.818) 
insert into KT values (852, 853.9, 852, 0.847, 0.846, 0.845, 0.843, 0.842, 0.84, 0.838, 0.837, 0.835, 0.833, 0.832, 0.83, 0.828, 0.826, 0.824, 0.823, 0.821, 0.819, 0.817, 0.815) 
insert into KT values (854, 855.9, 854, 0.843, 0.842, 0.841, 0.839, 0.838, 0.836, 0.835, 0.833, 0.831, 0.83, 0.828, 0.826, 0.824, 0.823, 0.821, 0.819, 0.817, 0.815, 0.813, 0.811) 
insert into KT values (856, 857.9, 856, 0.839, 0.838, 0.837, 0.835, 0.834, 0.832, 0.831, 0.829, 0.828, 0.826, 0.824, 0.822, 0.821, 0.819, 0.817, 0.815, 0.813, 0.811, 0.809, 0.808) 
insert into KT values (858, 859.9, 858, 0.835, 0.834, 0.833, 0.831, 0.83, 0.828, 0.827, 0.825, 0.824, 0.822, 0.82, 0.819, 0.817, 0.815, 0.813, 0.811, 0.81, 0.808, 0.806, 0.804) 
insert into KT values (860, 861.9, 860, 0.831, 0.83, 0.829, 0.828, 0.826, 0.825, 0.823, 0.822, 0.82, 0.818, 0.817, 0.815, 0.813, 0.811, 0.81, 0.808, 0.806, 0.804, 0.802, 0.8) 
insert into KT values (862, 863.9, 862, 0.828, 0.826, 0.825, 0.824, 0.822, 0.821, 0.819, 0.818, 0.816, 0.815, 0.813, 0.811, 0.809, 0.808, 0.806, 0.804, 0.802, 0.8, 0.799, 0.797) 
insert into KT values (864, 865.9, 864, 0.824, 0.823, 0.821, 0.82, 0.818, 0.817, 0.816, 0.814, 0.812, 0.811, 0.809, 0.807, 0.806, 0.804, 0.802, 0.801, 0.799, 0.797, 0.795, 0.793) 
insert into KT values (866, 867.9, 866, 0.82, 0.819, 0.817, 0.816, 0.815, 0.813, 0.812, 0.81, 0.809, 0.807, 0.806, 0.804, 0.802, 0.8, 0.799, 0.797, 0.795, 0.793, 0.792, 0.79) 
insert into KT values (868, 869.9, 868, 0.816, 0.815, 0.814, 0.812, 0.811, 0.81, 0.808, 0.807, 0.805, 0.803, 0.802, 0.8, 0.799, 0.797, 0.795, 0.793, 0.792, 0.79, 0.788, 0.786) 
insert into KT values (870, 871.9, 870, 0.812, 0.811, 0.81, 0.809, 0.807, 0.806, 0.804, 0.803, 0.801, 0.8, 0.798, 0.797, 0.795, 0.793, 0.792, 0.79, 0.788, 0.786, 0.785, 0.783) 
insert into KT values (872, 873.9, 872, 0.809, 0.807, 0.806, 0.805, 0.804, 0.802, 0.801, 0.799, 0.798, 0.796, 0.795, 0.793, 0.791, 0.79, 0.788, 0.786, 0.785, 0.783, 0.781, 0.779) 
insert into KT values (874, 875.9, 874, 0.805, 0.804, 0.803, 0.801, 0.8, 0.799, 0.797, 0.796, 0.794, 0.793, 0.791, 0.789, 0.788, 0.786, 0.785, 0.783, 0.781, 0.779, 0.778, 0.776) 
insert into KT values (876, 877.9, 876, 0.801, 0.8, 0.799, 0.798, 0.796, 0.795, 0.794, 0.792, 0.791, 0.789, 0.788, 0.786, 0.784, 0.783, 0.781, 0.779, 0.778, 0.776, 0.774, 0.772) 
insert into KT values (878, 879.9, 878, 0.798, 0.796, 0.795, 0.794, 0.793, 0.791, 0.79, 0.789, 0.787, 0.786, 0.784, 0.782, 0.781, 0.779, 0.778, 0.776, 0.774, 0.773, 0.771, 0.769) 
insert into KT values (880, 881.9, 880, 0.794, 0.793, 0.792, 0.79, 0.789, 0.788, 0.786, 0.785, 0.783, 0.782, 0.78, 0.779, 0.777, 0.776, 0.774, 0.772, 0.771, 0.769, 0.767, 0.766) 
insert into KT values (882, 883.9, 882, 0.79, 0.789, 0.788, 0.787, 0.786, 0.784, 0.783, 0.781, 0.78, 0.779, 0.777, 0.775, 0.774, 0.772, 0.771, 0.769, 0.767, 0.766, 0.764, 0.762) 
insert into KT values (884, 885.9, 884, 0.787, 0.786, 0.785, 0.783, 0.782, 0.781, 0.779, 0.778, 0.777, 0.775, 0.774, 0.772, 0.77, 0.769, 0.767, 0.766, 0.764, 0.762, 0.761, 0.759) 
insert into KT values (886, 887.9, 886, 0.783, 0.782, 0.781, 0.78, 0.778, 0.777, 0.776, 0.774, 0.773, 0.772, 0.77, 0.769, 0.767, 0.766, 0.764, 0.762, 0.761, 0.759, 0.757, 0.756) 
insert into KT values (888, 889.9, 888, 0.78, 0.779, 0.777, 0.776, 0.775, 0.774, 0.772, 0.771, 0.77, 0.768, 0.767, 0.765, 0.764, 0.762, 0.761, 0.759, 0.757, 0.756, 0.754, 0.753) 
insert into KT values (890, 891.9, 890, 0.776, 0.775, 0.774, 0.773, 0.772, 0.77, 0.769, 0.768, 0.766, 0.765, 0.763, 0.762, 0.76, 0.759, 0.757, 0.756, 0.754, 0.753, 0.751, 0.749) 
insert into KT values (892, 893.9, 892, 0.773, 0.772, 0.771, 0.769, 0.768, 0.767, 0.766, 0.764, 0.763, 0.761, 0.76, 0.758, 0.757, 0.755, 0.754, 0.752, 0.751, 0.749, 0.748, 0.746) 
insert into KT values (894, 895.9, 894, 0.769, 0.768, 0.767, 0.766, 0.765, 0.763, 0.762, 0.761, 0.759, 0.758, 0.757, 0.755, 0.754, 0.752, 0.751, 0.749, 0.748, 0.746, 0.744, 0.743) 
insert into KT values (896, 897.9, 896, 0.766, 0.765, 0.764, 0.762, 0.761, 0.76, 0.759, 0.757, 0.756, 0.755, 0.753, 0.752, 0.75, 0.749, 0.747, 0.746, 0.744, 0.743, 0.741, 0.74) 
insert into KT values (898, 899.9, 898, 0.762, 0.761, 0.76, 0.759, 0.758, 0.757, 0.755, 0.754, 0.753, 0.751, 0.75, 0.749, 0.747, 0.746, 0.744, 0.743, 0.741, 0.74, 0.738, 0.736) 
insert into KT values (900, 909.9, 900, 0.752, 0.751, 0.75, 0.749, 0.748, 0.747, 0.745, 0.744, 0.743, 0.742, 0.74, 0.739, 0.737, 0.736, 0.735, 0.733, 0.732, 0.73, 0.729, 0.727) 
insert into KT values (910, 919.9, 910, 0.736, 0.735, 0.734, 0.733, 0.732, 0.731, 0.729, 0.728, 0.727, 0.726, 0.724, 0.723, 0.722, 0.72, 0.719, 0.718, 0.716, 0.715, 0.713, 0.712) 
insert into KT values (920, 929.9, 920, 0.72, 0.719, 0.718, 0.717, 0.716, 0.715, 0.714, 0.713, 0.711, 0.71, 0.709, 0.708, 0.706, 0.705, 0.704, 0.702, 0.701, 0.7, 0.698, 0.697) 
insert into KT values (930, 939.9, 930, 0.705, 0.704, 0.703, 0.702, 0.701, 0.7, 0.699, 0.698, 0.696, 0.695, 0.694, 0.693, 0.692, 0.69, 0.689, 0.688, 0.687, 0.685, 0.684, 0.683) 
insert into KT values (940, 949.9, 940, 0.69, 0.689, 0.688, 0.687, 0.686, 0.685, 0.684, 0.683, 0.682, 0.681, 0.68, 0.678, 0.677, 0.676, 0.675, 0.674, 0.672, 0.671, 0.67, 0.669) 
insert into KT values (960, 969.9, 950, 0.675, 0.675, 0.674, 0.673, 0.672, 0.671, 0.67, 0.669, 0.668, 0.667, 0.666, 0.665, 0.663, 0.662, 0.661, 0.66, 0.659, 0.658, 0.656, 0.655) 
insert into KT values (960, 969.9, 960, 0.661, 0.681, 0.66, 0.659, 0.658, 0.657, 0.656, 0.655, 0.654, 0.653, 0.652, 0.651, 0.65, 0.649, 0.648, 0.647, 0.646, 0.644, 0.643, 0.642) 
insert into KT values (970, 979.9, 970, 0.648, 0.647, 0.646, 0.645, 0.645, 0.644, 0.643, 0.642, 0.641, 0.64, 0.639, 0.638, 0.637, 0.636, 0.635, 0.634, 0.633, 0.632, 0.631, 0.629) 
insert into KT values (980, 989.9, 980, 0.635, 0.634, 0.633, 0.632, 0.632, 0.631, 0.63, 0.629, 0.628, 0.627, 0.626, 0.625, 0.624, 0.623, 0.622, 0.621, 0.62, 0.619, 0.618, 0.617) 
insert into KT values (990, 999.9, 990, 0.622, 0.621, 0.621, 0.62, 0.619, 0.618, 0.617, 0.616, 0.616, 0.615, 0.614, 0.613, 0.612, 0.611, 0.61, 0.609, 0.608, 0.607, 0.606, 0.605) 


Хочу написать функцию, которая бы возвращала плотность жидкости в рабочих условиях из этой таблицы констант. Динамическим запросом можно вернуть так
declare @t float
declare @lpn float
declare @DynQuery nvarchar(max)
declare @RangeNumber int
declare @ParamDef nvarchar(500)
declare @kt float
/* Входные параметры для функции */
set @lpn = 978.99 -- лабораторная плотность при Т=20
set @t = 31 -- температура


set @RangeNumber = FLOOR(@t/5 + 1) -- попадаем в нужный температурный диапазон

set @DynQuery = N'select @kt = T' + CAST(@RangeNumber as varchar)+ ' from KT where FLOOR(' + CAST(@lpn as varchar) + ') between LPNL and LPNH'
set @ParamDef = N'@kt float OUTPUT'
exec sp_executesql @dynquery, @ParamDef, @kt = @kt output

select @kt

Помогите написать функцию, без использования динамических запросов.
24 сен 14, 11:40    [16614728]     Ответить | Цитировать Сообщить модератору
 Re: Выбор в таблице коэффициентов по номеру столбца без использования динамических запросов  [new]
Glory
Member

Откуда:
Сообщений: 104751
sanitar
Помогите написать функцию, без использования динамических запросов.

Ну так куча IF или CASE
24 сен 14, 11:41    [16614738]     Ответить | Цитировать Сообщить модератору
 Re: Выбор в таблице коэффициентов по номеру столбца без использования динамических запросов  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
Кто ж до такой структуры додумался-то!
Надо ж сначала подумать, как этим пользоваться!
Например, добавить колонку с номером измерения,
а вместо 20 колонок оставить одну.
Следующий шаг - из этих двух колонок сделать ещё одну таблицу,
ссылающуюся на исходную по ключевым полям, например, ([LPNL],[LPNH],[LPN]).
Или сделать суррогатный ключ.
Тогда и запросы сами собой получаться будут.
24 сен 14, 11:47    [16614782]     Ответить | Цитировать Сообщить модератору
 Re: Выбор в таблице коэффициентов по номеру столбца без использования динамических запросов  [new]
sanitar
Member

Откуда:
Сообщений: 66
Все понял, спасибо. Запросы пишу редко сейчас, а таблицу просто тупо из экселя загрузил. По сути, функция нужна для одноразового расчета и в дальнейшем использоваться никак не будет. Наколбасил диапазоны через IF.
24 сен 14, 11:58    [16614872]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить